Node.js MySQL - CONCAT()

In this post, we will discuss how to concatenate values in two or more columns of MySQL table in XAMPP Server through Node.js application with CONCAT() function

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

Command to install the mysql package:

npm install mysql
				

CONCAT() Function:

CONCAT() will combine two or more columns with a separator.It will take columns as parameter.

Syntax:

CONCAT(column1,column2,.....columnn)

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

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

Concat Example 1:-

Let's combine name and district column in village2 table with separator '-'.

// 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 concatenate name and district 
  //columns separated by '-'.
  connection_data.query("SELECT name,district,CONCAT(name,'-',district) 
  FROM village2", function (error, result) {
//Display the records one by one
    console.log(result);
  });
});

Output:

[
  RowDataPacket {
    name: 'pnp',
    district: 'hyd',
    "CONCAT(name,'-',district)": 'pnp-hyd'
  },
  RowDataPacket {
    name: 'pnp',
    district: 'hyd',
    "CONCAT(name,'-',district)": 'pnp-hyd'
  },
  RowDataPacket {
    name: 'bkpalem',
    district: 'np.sagar',
    "CONCAT(name,'-',district)": 'bkpalem-np.sagar'
  },
  RowDataPacket {
    name: 'ujjain',
    district: 'ujjain',
    "CONCAT(name,'-',district)": 'ujjain-ujjain'
  }
]

So, the two column values were combined with a separator '-'.

Concat Example 2:-

Let's combine name and district column in village2 table with separator ' AND '.

// 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 concatenate name and 
  //district columns separated by ' AND '.
  connection_data.query("SELECT name,district,CONCAT(name,' AND ',district) 
  FROM village2", function (error, result) {
//Display the records one by one
    console.log(result);
  });
});

Output:

[
  RowDataPacket {
    name: 'pnp',
    district: 'hyd',
    "CONCAT(name,' AND ',district)": 'pnp AND hyd'
  },
  RowDataPacket {
    name: 'pnp',
    district: 'hyd',
    "CONCAT(name,' AND ',district)": 'pnp AND hyd'
  },
  RowDataPacket {
    name: 'bkpalem',
    district: 'np.sagar',
    "CONCAT(name,' AND ',district)": 'bkpalem AND np.sagar'
  },
  RowDataPacket {
    name: 'ujjain',
    district: 'ujjain',
    "CONCAT(name,' AND ',district)": 'ujjain AND ujjain'
  }
]

So, the two column values were combined with a separator ' AND '.

Concat Example 3:-

Let's combine name and district column in village2 table with separator ' AND ' where name is 'pnp'.

// 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 concatenate name 
  //and district columns separated by ' AND '.
  connection_data.query("SELECT name,district,CONCAT(name,' AND ',district) 
  FROM village2 where name = 'pnp'", function (error, result) {
//Display the records one by one
    console.log(result);
  });
});

Output:

[
  RowDataPacket {
    name: 'pnp',
    district: 'hyd',
    "CONCAT(name,' AND ',district)": 'pnp AND hyd'
  },
  RowDataPacket {
    name: 'pnp',
    district: 'hyd',
    "CONCAT(name,' AND ',district)": 'pnp AND hyd'
  }
]

So, the two column values were combined with a separator ' AND ' with name as 'pnp'.

Summary

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