MySQL 字符串函数
函数 | 描述 | 实例 |
---|---|---|
ASCII(s) | 返回字符串 s 的第一个字符的 ASCII 码。 | mysql> select ascii('abc'); +--------------+ | ascii('abc') | +--------------+ | 97 | +--------------+ 1 row in set (0.00 sec) |
CHAR_LENGTH(s) | 返回字符串 s 的字符数 | mysql> select char_length('abc'); +--------------------+ | char_length('abc') | +--------------------+ | 3 | +--------------------+ 1 row in set (0.00 sec) |
CHARACTER_LENGTH(s) | 返回字符串 s 的字符数,等同于 CHAR_LENGTH(s) | mysql> select character_length('abc'); +-------------------------+ | character_length('abc') | +-------------------------+ | 3 | +-------------------------+ 1 row in set (0.00 sec) |
CONCAT(s1,s2...sn) | 字符串 s1,s2 等多个字符串合并为一个字符串 | 合并多个字符串 mysql> select concat("Hello"," World"); +--------------------------+ | concat("Hello"," World") | +--------------------------+ | Hello World | +--------------------------+ 1 row in set (0.00 sec) |
CONCAT_WS(x, s1,s2...sn) | 同 CONCAT(s1,s2,...) 函数,但是每个字符串之间要加上 x,x 可以是分隔符 | 合并多个字符串,并添加分隔符: mysql> select concat_ws("_"," Hello","world","mysql"); +-----------------------------------------+ | concat_ws("_"," Hello","world","mysql") | +-----------------------------------------+ | Hello_world_mysql | +-----------------------------------------+ 1 row in set (0.00 sec) |
FIELD(s,s1,s2...) | 返回第一个字符串 s 在字符串列表(s1,s2...)中的位置 | mysql> select field("world","hello","world","mysql"); +----------------------------------------+ | field("world","hello","world","mysql") | +----------------------------------------+ | 2 | +----------------------------------------+ 1 row in set (0.00 sec) |
FIND_IN_SET(s1,s2) | 返回在字符串s2中与s1匹配的字符串的位置 | mysql> select find_in_set("world","hello,world,mysql"); +------------------------------------------+ | find_in_set("world","hello,world,mysql") | +------------------------------------------+ | 2 | +------------------------------------------+ 1 row in set (0.00 sec) |
FORMAT(x,n) | 函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位,最后一位四舍五入。 | 格式化数字 "#,###.##" 形式: mysql> select format(3.1415926,2); +---------------------+ | format(3.1415926,2) | +---------------------+ | 3.14 | +---------------------+ 1 row in set (0.00 sec) |
INSERT(s1,x,len,s2) | 字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串 | mysql> select insert("baidu.com",1,5,"taobao"); +----------------------------------+ | insert("baidu.com",1,5,"taobao") | +----------------------------------+ | taobao.com | +----------------------------------+ 1 row in set (0.00 sec) |
LOCATE(s1,s) | 从字符串 s 中获取 s1 的开始位置 | mysql> select locate("world","hello world !"); +---------------------------------+ | locate("world","hello world !") | +---------------------------------+ | 7 | +---------------------------------+ 1 row in set (0.00 sec) |
LCASE(s) | 将字符串 s 的所有字母变成小写字母 | mysql> select lcase('Hello World'); +----------------------+ | lcase('Hello World') | +----------------------+ | hello world | +----------------------+ 1 row in set (0.00 sec) |
LEFT(s,n) | 返回字符串 s 的前 n 个字符 | mysql> select left('Hello World',3); +-----------------------+ | left('Hello World',3) | +-----------------------+ | Hel | +-----------------------+ 1 row in set (0.00 sec) |
LOWER(s) | 将字符串 s 的所有字母变成小写字母 | mysql> select lower('Hello World'); +----------------------+ | lower('Hello World') | +----------------------+ | hello world | +----------------------+ 1 row in set (0.00 sec) |
LPAD(s1,len,s2) | 在字符串 s1 的开始处填充字符串 s2,使字符串长度达到 len | mysql> select lpad('hello',7,'$'); +---------------------+ | lpad('hello',7,'$') | +---------------------+ | $$hello | +---------------------+ 1 row in set (0.00 sec) |
LTRIM(s) | 去掉字符串 s 开始处的空格 | 去掉字符串开始处的空格: mysql> select ltrim(" hello"); +--------------------+ | ltrim(" hello") | +--------------------+ | hello | +--------------------+ 1 row in set (0.00 sec) RUNOOB |
MID(s,n,len) | 从字符串 s 的 n 位置截取长度为 len 的子字符串,同 SUBSTRING(s,n,len) | : mysql> select mid('hello world!',3,5); +-------------------------+ | mid('hello world!',3,5) | +-------------------------+ | llo w | +-------------------------+ 1 row in set (0.00 sec)- UNO |
POSITION(s1 IN s) | 从字符串 s 中获取 s1 的开始位置 | mysql> select position('he'in 'hello'); +--------------------------+ | position('he'in 'hello') | +--------------------------+ | 1 | +--------------------------+ 1 row in set (0.00 sec) |
REPEAT(s,n) | 将字符串 s 重复 n 次 | 将字符串 重复三次: mysql> select repeat('hello ',3); +--------------------+ | repeat('hello ',3) | +--------------------+ | hello hello hello | +--------------------+ 1 row in set (0.00 sec) |
REPLACE(s,s1,s2) | 将字符串 s2 替代字符串 s 中的字符串 s1 | mysql> select replace('hello?','?','!'); +---------------------------+ | replace('hello?','?','!') | +---------------------------+ | hello! | +---------------------------+ 1 row in set (0.00 sec) |
REVERSE(s) | 将字符串s的顺序反过来 | 将字符串 的顺序反过来: mysql> select reverse('hello'); +------------------+ | reverse('hello') | +------------------+ | olleh | +------------------+ 1 row in set (0.00 sec) |
RIGHT(s,n) | 返回字符串 s 的后 n 个字符 | mysql> select right('hello',3); +------------------+ | right('hello',3) | +------------------+ | llo | +------------------+ 1 row in set (0.00 sec) |
RPAD(s1,len,s2) | 在字符串 s1 的结尾处添加字符串 s2,使字符串的长度达到 len | mysql> select rpad('hello',8,'!'); +---------------------+ | rpad('hello',8,'!') | +---------------------+ | hello!!! | +---------------------+ 1 row in set (0.00 sec) |
RTRIM(s) | 去掉字符串 s 结尾处的空格 | 去掉字符串末尾空格: mysql> select rtrim('hello '); +-------------------+ | rtrim('hello ') | +-------------------+ | hello | +-------------------+ 1 row in set (0.00 sec)NOOB |
SPACE(n) | 返回 n 个空格 | 返回 10 个空格: mysql> select space(10); +------------+ | space(10) | +------------+ | | +------------+ 1 row in set (0.01 sec) |
STRCMP(s1,s2) | 比较字符串 s1 和 s2,如果 s1 与 s2 相等返回 0 ,如果 s1>s2 返回 1,如果 s1<s2 返回 -1 | 比较字符串: mysql> select strcmp('hello','hello'); +-------------------------+ | strcmp('hello','hello') | +-------------------------+ | 0 | +-------------------------+ 1 row in set (0.00 sec) |
SUBSTR(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 | mysql> select substr('hello',2,3); +---------------------+ | substr('hello',2,3) | +---------------------+ | ell | +---------------------+ 1 row in set (0.00 sec) |
SUBSTRING(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串,等同于 SUBSTR(s, start, length) | mysql> select substring('hello',2,3); +------------------------+ | substring('hello',2,3) | +------------------------+ | ell | +------------------------+ 1 row in set (0.00 sec) |
SUBSTRING_INDEX(s, delimiter, number) | 返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。 如果 number 是正数,返回第 number 个字符左边的字符串。 如果 number 是负数,返回第(number 的绝对值(从右边数))个字符右边的字符串。 | mysql> select substring_index('hello','e',-1); +---------------------------------+ | substring_index('hello','e',-1) | +---------------------------------+ | llo | +---------------------------------+ 1 row in set (0.00 sec) mysql> select substring_index('hello','e',1); +--------------------------------+ | substring_index('hello','e',1) | +--------------------------------+ | h | +--------------------------------+ 1 row in set (0.00 sec) |
TRIM(s) | 去掉字符串 s 开始和结尾处的空格 | 去掉字符串的首尾空格: mysql> select trim(' hello '); +---------------------+ | trim(' hello ') | +---------------------+ | hello | +---------------------+ 1 row in set (0.00 sec) |
UCASE(s) | 将字符串转换为大写 | 将字符串 runoob 转换为大写: mysql> select ucase('Hello'); +----------------+ | ucase('Hello') | +----------------+ | HELLO | +----------------+ 1 row in set (0.00 sec) |
UPPER(s) | 将字符串转换为大写 | 将字符串 runoob 转换为大写: mysql> select upper('Hello'); +----------------+ | upper('Hello') | +----------------+ | HELLO | +----------------+ 1 row in set (0.00 sec) |