ORD() Function in MySQL
ORD() function in MySQL is used to find the code of the leftmost character in a string . If the leftmost character is not a multibyte character, it returns ASCII value. And if the leftmost character of the string str is a multibyte character, ORD returns the code for that character, calculated from the numeric values of its constituent bytes using this formula :
(1st byte code)+ (2nd byte code * 256)+ (3rd byte code * 256^2) ……
Syntax :
ORD( str )
Parameter : This function accepts one parameter as mentioned above and described below :
- str : Given string whose left most character code is to be find.
Returns : It returns the code of the leftmost character in a string.
Example-1 : Applying ORD() Function to a single character.
SELECT ORD('S') as Find_Code;
Output :
Find_Code |
---|
83 |
Example-2 : Applying ORD() Function to a String.
SELECT ORD('w3wiki') as Find_Code;
Output :
Find_Code |
---|
103 |
Example-3 : Applying ORD() Function to a number.
SELECT ORD(100) as Find_Code;
Output :
Find_Code |
---|
49 |
Example-4 :
The ORD function can also be used to find the code of the leftmost character of a column data. To demonstrate create a table named.
Player
CREATE TABLE Player( Player_id INT AUTO_INCREMENT, Player_name VARCHAR(100) NOT NULL, Playing_team VARCHAR(20) NOT NULL, PRIMARY KEY(Player_id ) );
Now inserting some data to the Player table :
INSERT INTO Player(Player_name ,Playing_team) VALUES ('Virat Kohli' , 'RCB' ) , ('Rohit Sharma' , 'MI' ) , ('Dinesh Karthik', 'KKR' ) , ('Shreyash Iyer' , 'DC' ) , ('David Warner' , 'SRH' ) , ('Steve Smith' , 'RR' ) , ('Andre Russell' , 'KKR' ) , ('Jasprit Bumrah' , 'MI' ) , ('Risabh Panth', 'DC' ) ;
So, the Player Table is :
mysql> SELECT * FROM Player; +-----------+----------------+--------------+ | Player_id | Player_name | Playing_team | +-----------+----------------+--------------+ | 1 | Virat Kohli | RCB | | 2 | Rohit Sharma | MI | | 3 | Dinesh Karthik | KKR | | 4 | Shreyash Iyer | DC | | 5 | David Warner | SRH | | 6 | Steve Smith | RR | | 7 | Andre Russell | KKR | | 8 | Jasprit Bumrah | MI | | 9 | Risabh Panth | DC | +-----------+----------------+--------------+
Now, we will apply ORD function to find the code of the leftmost character of column Player_name and Playing_team.
Select * , ORD(Player_name) , ORD(Playing_team) FROM Player;
Output :
+-----------+----------------+--------------+------------------+-------------------+ | Player_id | Player_name | Playing_team | ORD(Player_name) | ORD(Playing_team) | +-----------+----------------+--------------+------------------+-------------------+ | 1 | Virat Kohli | RCB | 86 | 82 | | 2 | Rohit Sharma | MI | 82 | 77 | | 3 | Dinesh Karthik | KKR | 68 | 75 | | 4 | Shreyash Iyer | DC | 83 | 68 | | 5 | David Warner | SRH | 68 | 83 | | 6 | Steve Smith | RR | 83 | 82 | | 7 | Andre Russell | KKR | 65 | 75 | | 8 | Jasprit Bumrah | MI | 74 | 77 | | 9 | Risabh Panth | DC | 82 | 68 | +-----------+----------------+--------------+------------------+-------------------+