Node.js MySQL - LEFT()

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

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

Command to install the mysql package:

npm install mysql
				

LEFT() Function:

LEFT() will return the substring from the left in a string type column upto particular characters from a table. It will take two parameters.

Syntax:

LEFT(column, length)

1. The first parameter is the column name that hold string type values.
2. The second parameter specifies the numner of characters to be returned from left.

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

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

LEFT Example 1:-

Let's return the substring from left in description upto 5th character.

// 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 from left.
  connection_data.query("SELECT description, LEFT(description,5) 
  FROM village2 ", function (error, result) {
 
//Display the records one by one
    console.log(result);
  });
});

Output:

[
  RowDataPacket {
    description: 'Hello GKIndex Welcome to my tutorial',
    'LEFT(description,5)': 'Hello'
  },
  RowDataPacket {
    description: 'Hello GKIndex Welcome to my tutorial',
    'LEFT(description,5)': 'Hello'
  },
  RowDataPacket {
    description: 'Hello Node.js',
    'LEFT(description,5)': 'Hello'
  },
  RowDataPacket {
    description: 'Hello Ujjain',
    'LEFT(description,5)': 'Hello'
  }
]

So from left, the substrings upto 5th character were returned. Indirectly the length of the substring is equal to the length.

LEFT Example 2:-

Let's return the substring from left in description upto 10th character.

// 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 from left.
  connection_data.query("SELECT description, LEFT(description,10) 
  FROM village2 ", function (error, result) {
 
//Display the records one by one
    console.log(result);
  });
});

Output:

[
  RowDataPacket {
    description: 'Hello GKIndex Welcome to my tutorial',
    'LEFT(description,10)': 'Hello GKIn'
  },
  RowDataPacket {
    description: 'Hello GKIndex Welcome to my tutorial',
    'LEFT(description,10)': 'Hello GKIn'
  },
  RowDataPacket {
    description: 'Hello Node.js',
    'LEFT(description,10)': 'Hello Node'
  },
  RowDataPacket {
    description: 'Hello Ujjain',
    'LEFT(description,10)': 'Hello Ujja'
  }
]

So from left, the substrings upto 10th character were returned. It will consider single space also as one character.

LEFT Example 3:-

Let's return the substring from left in district column upto 3rd character where district is 'np.sagar'.

// 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 from left.
  connection_data.query("SELECT district, LEFT(district,3) FROM 
  village2 where district='np.sagar'", function (error, result) {
 
//Display the records one by one
    console.log(result);
  });
});

Output:

[ RowDataPacket { district: 'np.sagar', 'LEFT(district,3)': 'np.' } ]

So from left, there is only one string that matches the condition and three characters were returned from left.

Summary

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