UNICODE Function in SQL

The UNICODE function of Structured Query Language shows the unicode (integer) value of the first character of the string. We can also use the UNICODE function with the string fields of the SQL table.

Syntax of UNICODE String Function

Syntax1: This syntax uses the UNICODE function with the column names of the SQL table:

In the syntax, we have to specify the column’s name on which we want to use the UNICODE string function.

Syntax2: This syntax uses the UNICODE function with the set of characters (string):

Syntax2: This syntax uses the UNICODE function with the individual character:

Examples of UNICODE String function

Example 1: The following SELECT query shows the UNICODE value of the given string:

Output:

ASCII_J
74

Example 2: The following SELECT query returns the UNICODE value of @ symbol:

Output:

[email protected]
64

Example 3: The following SELECT query shows the UNICODE value of the first character of the given string:

Output:

ASCII_N
78

Example 3: The following SELECT query shows the UNICODE value of the ‘R’ character:

Output:

ASCII_R
82

Example 4: This example uses the UNICODE function with the SQL table

In this example, we are going to create a new SQL table on which we have to execute the UNICODE function for finding the UNICODE value of first character.

The syntax for creating the new table in the SQL database is as follows:

  CREATE TABLE table_name  (  First_Column_of_SQLtable Data Type (character_size of First Column),    Second_Column_of_SQLtable Data Type (character_size of the Second column ),    Third_Column_of_SQLtable Data Type (character_size of the Third column),    …      Last_Column_of_table Data Type (character_size of the Last column)  );   

The following CREATE statement creates the Student_Grade table:

  CREATE TABLE Student_Grade  (  Roll_No INT PRIMARY KEY,    First_Name VARCHAR (100),    Last_Name VARCHAR (100),   First_City Varchar(120),  Second_City Varchar(120),  New_City Varchar(120),  Hindi_Marks INT,   Maths_Marks INT,   Grade Varchar (80)  );  

The below INSERT queries insert the records of students with grades and marks in the Student_Grade table:

  INSERT INTO Student_Grade (Roll_No, First_Name, Last_Name, First_City, Second_City, New_City, Hindi_Marks, Maths_Marks, Grade) VALUES (10, Aman, Sharma, Lucknow Chandigarh, Ghaziabad, 88, 95, A2);    INSERT INTO Student_Grade   (Roll_No, First_Name, Last_Name, First_City, Second_City, New_City, Hindi_Marks, Maths_Marks, Grade)   VALUES ( 02, Vishal, Sharma, Chandigarh, Ghaziabad, Delhi, 95, 82, A1 );    INSERT INTO Student_Grade   (Roll_No, First_Name, Last_Name, First_City, Second_City, New_City, Hindi_Marks, Maths_Marks, Grade)   VALUES (07, Raj, Gupta, Delhi, Ghaziabad, Lucknow, 91, 95, A1);    INSERT INTO Student_Grade   (Roll_No, First_Name, Last_Name, First_City, Second_City, New_City, Hindi_Marks, Maths_Marks, Grade)    VALUES (04, Yash, Singhania, Ghaziabad, Delhi, Lucknow, 85, 82, A2);    INSERT INTO Student_Grade   (Roll_No, First_Name, Last_Name, First_City, Second_City, New_City, Hindi_Marks, Maths_Marks, Grade)   VALUES (11, Vinay, Roy, Delhi, Kanpur, Ghaziabad, 95, 97, A1);    INSERT INTO Student_Grade   (Roll_No, First_Name, Last_Name, First_City, Second_City, New_City, Hindi_Marks, Maths_Marks, Grade)   VALUES (16, Manoj, Gupta, Ghaziabad, Meerut, Chandigarh, 95, 90, B1);      INSERT INTO Student_Grade   (Roll_No, First_Name, Last_Name, First_City, Second_City, New_City, Hindi_Marks, Maths_Marks, Grade)   VALUES (19, Ram, Gupta, Lucknow, Ghaziabad, Chandigarh, 89, 95, A2);  

The following SELECT statement displays the inserted records of the above Student_Grade table:

Roll_No	First_Name	Last_Name	First_City	Second_City	New_City	Hindi_Marks	Maths_Marks	Grade
10	Aman	Sharma	Lucknow	Chandigarh	Ghaziabad	88	95	A2
02	Vishal	Sharma	Chandigarh	Ghaziabad	Ghaziabad	95	82	A1
07	Raj	Gupta	Delhi	Ghaziabad	Lucknow	91	95	A1
04	Yash	Singhania	Ghaziabad	Delhi	Lucknow	85	82	A2
11	Vinay	Roy	Delhi	Kanpur	Ghaziabad	95	97	A1
16	Manoj	Gupta	Ghaziabad	Meerut	Chandigarh	95	90	B1
19	Ram	Gupta	Lucknow	Ghaziabad	Chandigarh	89	95	A2

Query 1: The following SELECT query uses the UNICODE function with the Last_Name column of the above Student_Grade table:

This SQL statement shows the unicode value of the first character of the last name from the table.

Output:

Last_Name	UNICODE _LastName
SHARMA	83
SHARMA	83
GUPTA	71
SINGHANIA	83
ROY	82
GUPTA	71
GUPTA	71

Query 2: The following SELECT query uses the UNICODE function with the First_City, Second_City, and New_City columns of those students whose Roll_No is greater than 2 in the above Student_Grade table:

  SELECT Roll_No, UNICODE (First_City), UNICODE (Second_City), UNICODE (New_City) FROM Student_Grade WHERE Roll_No > 2;  

Output:

Roll_No	ASCII(First_City)	ASCII(Second_City)	ASCII(New_City)
07	68	71	76
04	71	68	76
11	68	75	71
16	71	77	67
19	76	71	67

Query 3: The following SELECT query uses the UNICODE function with the First_Name column of the above Student_Grade table:

  SELECT First_Name, UNICODE (First_Name) AS UNICODE _FirstName FROM Student_Grade;  

This SQL statement shows the unicode value of the first character of the first name from the table.

Output:

First_Name	UNICODE _FirstName
Aman	65
Vishal	86
Raj	82
Yash	90
Vinay	86
Manoj	77
Ram	82

Next TopicREPLICATE Function in SQL

UNICODE Function in SQL

UNICODE Function in SQL

Syntax of UNICODE String Function

Examples of UNICODE String function

SUM Function in SQL

Event Listener

You may also like