Mysql SQL LENGTH, RIGHT, CHAR_LENGTH Character Processing Function

Mysql tutorial SQL LENGTH, RIGHT, CHAR_LENGTH Character Processing Function

LENGTH (str)
　
OCTET_LENGTH (str)
　
CHAR_LENGTH (str)
　
CHARACTER_LENGTH (str)
Returns the length of the str string.

Mysql> select LENGTH ('text ');
-> 4
Mysql> select OCTET_LENGTH ('text ');
-> 4

Mysql> SELECT
-> CONCAT (RIGHT (Name, LENGTH (Name)-INSTR (Name, '') + 1 ),
-> ',', LEFT (Name, INSTR (Name, '')-1 ))
-> AS StudentName
-> FROM Student
-> Order by StudentName;
+ --------------- +
| StudentName |
+ --------------- +
| But, Cory |
| Harvests, JJ |
| Wang, Joe |
+ --------------- +
3 rows in set (0.04 sec)

*/

RIGHT (str, len)
Returns the rightmost len character of the str string.
Mysql> select RIGHT ('foobarbar', 4 );
-> 'Rbar'

This function is multi-byte reliable

 

/* Prepare the data */
Drop table Student;

Create table Student (
StudentID int not null primary key,
Name VARCHAR (50) NOT NULL
) TYPE = InnoDB;

/* Insert data for testing */
Insert into Student (StudentID, Name) VALUES (1, 'Joe Wang ');
Insert into Student (StudentID, Name) VALUES (2, 'Cory ');
Insert into Student (StudentID, Name) VALUES (3, 'JJ Harvests ');

Select * from StudentExam;

/* Real command */
SELECT
CONCAT (RIGHT (Name, LENGTH (Name)-INSTR (Name, '') + 1 ),
',', LEFT (Name, INSTR (Name, '')-1 ))
AS StudentName
FROM Student
Order by StudentName;

 

Note that for multi-byte characters, its CHAR_LENGTH () is calculated only once.

 

SELECT Name, CHAR_LENGTH (Name) AS CharLength
FROM DVDs
WHERE CHAR_LENGTH (Name)> 5
Order by Name;