Node.js MySQL - REPLACE()

In this post, we will discuss how to replace the values in a MySQL table in XAMPP Server using Node.js with REPLACE() function.

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

Command to install the mysql package:

npm install mysql
				

REPLACE() Function:

REPLACE() is used to replace the particular characters in string values of a column with new set of charaters. It will take three parameters.

Syntax:

REPLACE(column, replace, new)

1. The first parameter is the column name that hold string type values.
2. The second parameter is the string to be replaced.
3. The last parameter is the string that will replace the string passed as second parameter.

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

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

REPLACE Example 1:-

Let's replace the string - 'Hello' with 'Hi' in description column.

// 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 replaced values
  connection_data.query("SELECT description, REPLACE(description,'Hello','Hi') 
  FROM village2", function (error, result) {
//Display the records one by one
    console.log(result);
  });
});

Output:

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

We can see that wherever 'Hello' in decsription column is replaced with 'Hi'.

REPLACE Example 2:-

Let's replace the string - 'my' with 'our' in description column.

// 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 replaced values
  connection_data.query("SELECT description, REPLACE(description,'my','our') 
  FROM village2", function (error, result) {
//Display the records one by one
    console.log(result);
  });
});

Output:

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

We can see that wherever 'my' in decsription column is replaced with 'our'.

REPLACE Example 3:-

Let's replace the character - 'p' with 'P' in name column where name is 'bkpalem'..

// 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 replaced values
  connection_data.query("SELECT name, REPLACE(name,'p','P') 
  FROM village2 where name='bkpalem'", function (error, result) {
//Display the records one by one
    console.log(result);
  });
});

Output:

[
  RowDataPacket { name: 'bkpalem', "REPLACE(name,'p','P')": 'bkPalem' }
]

We can see that there is only one row with name - 'bkpalem'.'p' is replaced with 'P'.

Summary

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