mysql> select trim(' bar '); +-----------------+ | trim(' bar ') | +-----------------+ | bar | +-----------------+ mysql> select trim(leading 'x' from 'xxxbarxxx'); +------------------------------------+ | trim(leading 'x' from 'xxxbarxxx') | +------------------------------------+ | barxxx | +------------------------------------+ mysql> select trim(both 'x' from 'xxxbarxxx'); +---------------------------------+ | trim(both 'x' from 'xxxbarxxx') | +---------------------------------+ | bar | +---------------------------------+ mysql> select trim(trailing 'xyz' from 'barxxyz'); +-------------------------------------+ | trim(trailing 'xyz' from 'barxxyz') | +-------------------------------------+ | barx | +-------------------------------------+
12、REPLACE(str, from_str, to_str)：在源字符串str中查找所有的子串form_str（大小写敏感），找到后使用替代字符串to_str替换它。返回替换后的字符串
mysql> select replace('www.mysql.com','w','Ww'); +-----------------------------------+ | replace('www.mysql.com','w','Ww') | +-----------------------------------+ | WwW | +-----------------------------------+
13、LTRIM(str)，RTRIM(str)：去掉字符串的左边或右边的空格(左对齐、右对齐)
mysql> SELECT ltrim(' barbar ') rs1, rtrim(' barbar ') rs2; +-----------+-----------+ | rs1 | rs2 | +-----------+-----------+ | barbar | barbar | +-----------+-----------+
14、REPEAT(str, count)：将字符串str重复count次后返回
mysql> select repeat('MySQL',3); +-------------------+ | repeat('MySQL',3) | +-------------------+ | MySQLMySQLMySQL | +-------------------+
15、REVERSE(str)：将字符串str反转后返回
mysql> select reverse('abcdef'); +-------------------+ | reverse('abcdef') | +-------------------+ | fedcba | +-------------------+
16、CHAR(N,... [USING charset_name])：将每个参数N解释为整数（字符的编码），并返回每个整数对应的字符所构成的字符串(NULL值被忽略)。
mysql> select char(77,121,83,81,'76'),char(77,77.3,'77.3'); +-------------------------+----------------------+ | char(77,121,83,81,'76') | char(77,77.3,'77.3') | +-------------------------+----------------------+ | MySQL | MMM | +-------------------------+----------------------+
默认情况下，函数返回二进制字符串，若想返回针对特定字符集的字符串，使用using选项
mysql> SELECT charset(char(0x65)), charset(char(0x65 USING utf8)); +---------------------+--------------------------------+ | charset(char(0x65)) | charset(char(0x65 USING utf8)) | +---------------------+--------------------------------+ | binary | utf8 | +---------------------+--------------------------------+
17、FORMAT(X,D[,locale])：以格式‘#,###,###.##’格式化数字X
D指定小数位数
locale指定国家语言(默认的locale为en_US)
mysql> SELECT format(12332.123456, 4),format(12332.2,0); +-------------------------+-------------------+ | format(12332.123456, 4) | format(12332.2,0) | +-------------------------+-------------------+ | 12,332.1235 | 12,332 | +-------------------------+-------------------+ mysql> SELECT format(12332.2,2,'de_DE'); +---------------------------+ | format(12332.2,2,'de_DE') | +---------------------------+ | 12.332,20 | +---------------------------+
18、SPACE(N)：返回由N个空格构成的字符串
mysql> select space(3); +----------+ | space(3) | +----------+ | | +----------+
19、LEFT(str, len)：返回最左边的len长度的子串
mysql> select left('chinaitsoft',5); +-----------------------+ | left('chinaitsoft',5) | +-----------------------+ | china | +-----------------------+
20、RIGHT(str, len)：返回最右边的len长度的子串
mysql> select right('chinaitsoft',5); +------------------------+ | right('chinaitsoft',5) | +------------------------+ | tsoft | +------------------------+
21、STRCMP(expr1,expr2)：如果两个字符串是一样的则返回0；如果第一个小于第二个则返回-1；否则返回1