Node.js MySQL - POSITION()

In this post, we will discuss how to search the substring in a string in MySQL XAMPP Server through Node.js application with POSITION() function

It is important to install mysql package in node.js.

Command to install the mysql package:

npm install mysql
				

POSITION() Function:

POSITION() will check if substring present in the values in the column of table. If the substring is present, then it will return the substring's first character position in the actual column value. If the substring not present, then 0 is returned.IN operator is used for it.

Syntax:

POSITION(sub_string IN column)

Steps for Node.js script:

Now let's see steps

1. First start your XAMPP Server (Both Apache and MySQL).
2. Open Notepad or any text-editor and write the Node.js script
3. In that script, first we have to load the mysql package using the below syntax
var mysql_package = require('mysql');
4. Create the connection using the server,username and password.
Copied

var connection_data = mysql_package.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database:"database_name"
});

5. Write the sql query that uses POSITION() function
Copied

connection_data.connect(function(error) {
  connection_data.query("SELECT POSITION(sub_string IN column),.... 
  FROM table_name WHERE condition/s...", function (error, result) {
     console.log(result);
  });
});

6. Now type the following command in your command prompt to run the script.
node file_name.js

Consider the village2 table with the following records:

POSITION Example 1:-

Let's check for substring-'np' in name column of village2 table.

// Load the mysql package
var mysql_package = require('mysql');
// Create the connection using the server,username and password.
//In my scenario - server is the localhost,
//username is root,
//password is empty.
//database is facility
var connection_data = mysql_package.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database:"facility"
});
connection_data.connect(function(error) {  
  // Write sql query that uses POSITION() function
  connection_data.query("SELECT name, POSITION('np'IN name) from village2;",
   function (error, result) {
//Display the records one by one
    console.log(result);
  });
});

Output:

[
  RowDataPacket { name: 'pnp', "POSITION('np'IN name)": 2 },
  RowDataPacket { name: 'pnp', "POSITION('np'IN name)": 2 },
  RowDataPacket { name: 'bkpalem', "POSITION('np'IN name)": 0 },
  RowDataPacket { name: 'ujjain', "POSITION('np'IN name)": 0 }
]

So, the substring-'np' is present in first 2 rows of the name column. and it is occured from 2nd position. Hence 2 is returned. For remaining 2 rows, there is no substring matched with 'np'. Hence 0 is returned.

POSITION Example 2:-

Let's check for substring-'Welcome' in description column of village2 table.

// Load the mysql package
var mysql_package = require('mysql');
// Create the connection using the server,username and password.
//In my scenario - server is the localhost,
//username is root,
//password is empty.
//database is facility
var connection_data = mysql_package.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database:"facility"
});
connection_data.connect(function(error) {  
  // Write sql query that uses POSITION() function
  connection_data.query("SELECT description, POSITION('Welcome'IN description) 
  from village2;", function (error, result) {
//Display the records one by one
    console.log(result);
  });
});

Output:

[
  RowDataPacket {
    description: 'Hello GKIndex Welcome to my tutorial',
    "POSITION('Welcome'IN description)": 15
  },
  RowDataPacket {
    description: 'Hello GKIndex Welcome to my tutorial',
    "POSITION('Welcome'IN description)": 15
  },
  RowDataPacket {
    description: 'Hello Node.js',
    "POSITION('Welcome'IN description)": 0
  },
  RowDataPacket {
    description: 'Hello Ujjain',
    "POSITION('Welcome'IN description)": 0
  }
]

So, the substring-'Welcome' is present in first 2 rows of the name column. and it is occured from 15th position. Hence 15 is returned. For remaining 2 rows, there is no substring matched with 'Welcome'. Hence 0 is returned.

Summary

In this post, we seen how to use POSITION() function on MySQL table in XAMPP Server.