mysql教程 sql LENGTH,RIGHT,CHAR_LENGTH字符处理函数

LENGTH(str)
　
OCTET_LENGTH(str)
　
CHAR_LENGTH(str)
　
CHARACTER_LENGTH(str)
返回字符串str的长度。

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)
返回字符串str的最右面len个字符。
mysql> select RIGHT('foobarbar', 4);
        -> 'rbar'

该函数是多字节可靠的

/* 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 But');
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;

注意，对于多字节字符，其CHAR_LENGTH()仅计算一次。

SELECT Name, CHAR_LENGTH(Name) AS CharLength
FROM DVDs
WHERE CHAR_LENGTH(Name)>5
ORDER BY Name;