Node.js MySQL - SUBSTRING()

In this post, we will discuss how to select substring from a column in a MySQL table from XAMPP Server using Node.js with SUBSTRING() function

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

Command to install the mysql package:

npm install mysql
				

SUBSTRING() Function

SUBSTRING() will return the substring from the string type column in a table. It will take three parameters.

Syntax:

SUBSTRING(column, start_pos,length)

1. The first parameter is the column name that hold string type values.
2. The second parameter specifies the string starting position(Integer) in which the string will return from this point. Here, the character first position is 1.
3. The last parameter is the length which is an integer that specifies the number of characters to be returned from the start_pos.

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 SUBSTRING() function
Copied

connection_data.connect(function(error) {
  connection_data.query("SELECT SUBSTRING(column,start_pos,length),.... 
  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:

SUBSTRING Example 1:-

Let's return the substring in description column from 4th position up to length-8.

// 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 to return substrings.
  connection_data.query("SELECT description, SUBSTRING(description,4,8) 
  FROM village2", function (error, result) { 
//Display the records one by one
    console.log(result);
  });
});

Output:

[
  RowDataPacket {
    description: 'Hello GKIndex Welcome to my tutorial',
    'SUBSTRING(description,4,8)': 'lo GKInd'
  },
  RowDataPacket {
    description: 'Hello GKIndex Welcome to my tutorial',
    'SUBSTRING(description,4,8)': 'lo GKInd'
  },
  RowDataPacket {
    description: 'Hello Node.js',
    'SUBSTRING(description,4,8)': 'lo Node.'
  },
  RowDataPacket {
    description: 'Hello Ujjain',
    'SUBSTRING(description,4,8)': 'lo Ujjai'
  }
]

So from the 4th position, the substrings were returned for all rows with length-8. It will consider single space as one character.

SUBSTRING Example 2:-

Let's return the substring in name column from 1st position upto length-2.

// 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 to return substrings.
  connection_data.query("SELECT name, SUBSTRING(name,1,2) 
  FROM village2", function (error, result) {
//Display the records one by one
    console.log(result);
  });
});

Output:

[
  RowDataPacket { name: 'pnp', 'SUBSTRING(name,1,2)': 'pn' },
  RowDataPacket { name: 'pnp', 'SUBSTRING(name,1,2)': 'pn' },
  RowDataPacket { name: 'bkpalem', 'SUBSTRING(name,1,2)': 'bk' },
  RowDataPacket { name: 'ujjain', 'SUBSTRING(name,1,2)': 'uj' }
]

So from the 1st position, the substrings were returned for all rows with length-2.

SUBSTRING Example 3:-

Let's return the substring in name column from 1st position upto length-2 where name is 'ujjain'.

// 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 to return substrings.
  connection_data.query("SELECT name, SUBSTRING(name,1,2) 
  FROM village2 where name='ujjain'", function (error, result) {
 
//Display the records one by one
    console.log(result);
  });
});

Output:

[ RowDataPacket { name: 'ujjain', 'SUBSTRING(name,1,2)': 'uj' } ]

So from the 1st position, the substrings were returned for all rows with length-2 where name is 'ujjain'.

Summary

In this post, we seen how to use SUBSTRING() function on MySQL table in XAMPP Server and also it can be possible to specify WHERE clause along this function.