Mysql String Functions

SUBSTRING_INDEX(str,delim,count)

按标识符截取指定长度的字符串

1 mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
2         -> 'www.mysql'
3 mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
4         -> 'mysql.com'

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)

删除字符串前后的特定字符或者字符串，默认删除空格，默认两端都删

1 mysql> SELECT TRIM('  bar   ');
2         -> 'bar'
3 mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
4         -> 'barxxx'
5 mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
6         -> 'bar'
7 mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
8         -> 'barx'

CONCAT(str1,str2,...)

连接子字符串，如果有一个字符串为空，将返回NULL

1 mysql> SELECT CONCAT('My', 'S', 'QL');
2         -> 'MySQL'
3 mysql> SELECT CONCAT('My', NULL, 'QL');
4         -> NULL
5 mysql> SELECT CONCAT(14.3);
6         -> '14.3'
7 mysql> SELECT 'My' 'S' 'QL';
8         -> 'MySQL'

FORMAT(X,D[,locale])

1 mysql> SELECT FORMAT(12332.123456, 4);
2         -> '12,332.1235'
3 mysql> SELECT FORMAT(12332.1,4);
4         -> '12,332.1000'
5 mysql> SELECT FORMAT(12332.2,0);
6         -> '12,332'
7 mysql> SELECT FORMAT(12332.2,2,'de_DE');
8         -> '12.332,20'