Node.js - MySQL UPPER,LOWER

In this post, we will discuss how to return the values in uppercase and in lowercase for Node.js MySql Functions.

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

Command to install the mysql package:

npm install mysql
				

UPPER() Function:

UPPER() is used to return the values in a column in upper case.

LOWER() Function

LOWER() is used to return the values in a column in lower case.

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 UPPER/LOWER.
Copied

//UPPER(Column)
connection_data.connect(function(error) {
connection_data.query("SELECT UPPER(column),.... 
from table_name", function (error, result) {
    console.log(result);
  });
});
//LOWER(Column)
connection_data.connect(function(error) {
connection_data.query("SELECT LOWER(column),.... 
from table_name", 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 field table with the following records:

Example 1:- UPPER

Let's select the district and field_type column rows in Upper Case.

// 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 district and field_type 
//values in upper case.
  connection_data.query("SELECT UPPER(district),UPPER(field_type) 
  from field", function (error, result) {
	  
//Display the records one by one
    console.log(result);
 
  });
});

Output:

[
  RowDataPacket {
    'UPPER(district)': 'GUNTUR',
    'UPPER(field_type)': 'BLACK'
  },
  RowDataPacket {
    'UPPER(district)': 'GUNTUR',
    'UPPER(field_type)': 'RED'
  },
  RowDataPacket {
    'UPPER(district)': 'GUNTUR',
    'UPPER(field_type)': 'BLACK'
  },
  RowDataPacket {
    'UPPER(district)': 'NELLORE',
    'UPPER(field_type)': 'SAND'
  },
  RowDataPacket {
    'UPPER(district)': 'NELLORE',
    'UPPER(field_type)': 'SAND'
  },
  RowDataPacket {
    'UPPER(district)': 'GUNTUR',
    'UPPER(field_type)': 'BLACK'
  },
  RowDataPacket {
    'UPPER(district)': 'KADAPA',
    'UPPER(field_type)': 'RED'
  },
  RowDataPacket {
    'UPPER(district)': 'KADAPA',
    'UPPER(field_type)': 'SAND'
  },
  RowDataPacket {
    'UPPER(district)': 'VISAKHA',
    'UPPER(field_type)': 'SAND'
  },
  RowDataPacket {
    'UPPER(district)': 'ONGOLE',
    'UPPER(field_type)': 'RED'
  },
  RowDataPacket {
    'UPPER(district)': 'VISAKHA',
    'UPPER(field_type)': 'SAND'
  }
]

So, we can see that rows are returned in upper case.

Example 2:- LOWER

Let's select the district and field_type column rows in Lower Case.

// 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 district and field_type 
//values in lower case.
  connection_data.query("SELECT LOWER(district),LOWER(field_type) 
  from field", function (error, result) {
	  
//Display the records one by one
    console.log(result);
 
  });
});

Output:

[
  RowDataPacket {
    'LOWER(district)': 'guntur',
    'LOWER(field_type)': 'black'
  },
  RowDataPacket {
    'LOWER(district)': 'guntur',
    'LOWER(field_type)': 'red'
  },
  RowDataPacket {
    'LOWER(district)': 'guntur',
    'LOWER(field_type)': 'black'
  },
  RowDataPacket {
    'LOWER(district)': 'nellore',
    'LOWER(field_type)': 'sand'
  },
  RowDataPacket {
    'LOWER(district)': 'nellore',
    'LOWER(field_type)': 'sand'
  },
  RowDataPacket {
    'LOWER(district)': 'guntur',
    'LOWER(field_type)': 'black'
  },
  RowDataPacket {
    'LOWER(district)': 'kadapa',
    'LOWER(field_type)': 'red'
  },
  RowDataPacket {
    'LOWER(district)': 'kadapa',
    'LOWER(field_type)': 'sand'
  },
  RowDataPacket {
    'LOWER(district)': 'visakha',
    'LOWER(field_type)': 'sand'
  },
  RowDataPacket {
    'LOWER(district)': 'ongole',
    'LOWER(field_type)': 'red'
  },
  RowDataPacket {
    'LOWER(district)': 'visakha',
    'LOWER(field_type)': 'sand'
  }
]

So, we can see that rows are returned in lower case.

Summary

In this post, we seen how to use mysql UPPER(),LOWER() functions in Node.js Script with each one example.