MySQL中字符串类型的常用函数

ascii(s)

返回字符串S中的第一个字符的ASCII码值

mysql> select ascii('a') from dual;
+------------+
| ascii('a') |
+------------+
|         97 |
+------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

char_length(s)

返回字符串s的字符数。作用与character_length(s)相同

mysql> select char_length('hello'),char_length('我们')
    -> from dual;
+----------------------+---------------------+
| char_length('hello') | char_length('我们') |
+----------------------+---------------------+
|                    5 |                   2 |
+----------------------+---------------------+
1 row in set (0.06 sec)

1
2
3
4
5
6
7
8
9

length(s)

返回字符串s的字节数，和字符集有关

mysql> select length('hello'),length('我们')
    -> from dual;
+-----------------+----------------+
| length('hello') | length('我们') |
+-----------------+----------------+
|               5 |              4 |
+-----------------+----------------+
1 row in set (0.05 sec)
1
2
3
4
5
6
7
8

concat(s1,s2,…,sn)

连接s1,s2,…,sn为一个字符串

mysql> select concat( '你好','黄壮') from dual;
+------------------------+
| concat( '你好','黄壮') |
+------------------------+
| 你好黄壮               |
+------------------------+
1 row in set (0.12 sec)
1
2
3
4
5
6
7

concat_ws(x,s1,s2,…,sn)

也是拼接字符串，但是每个字符串之间要加上x

mysql> select concat_ws('-','hello','China') as "拼接后效果";
+-------------+
| 拼接后效果  |
+-------------+
| hello-China |
+-------------+
1 row in set (0.10 sec)
1
2
3
4
5
6
7

insert(str, idx, len,replacestr)

将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr

mysql> select insert('hello',2,3,'hz')
    -> from dual;
+--------------------------+
| insert('hello',2,3,'hz') |
+--------------------------+
| hhzo                     |
+--------------------------+
1 row in set (0.13 sec)
1
2
3
4
5
6
7
8

MySQL中，字符串的位置是从1开始的。

replace(str, a, b)

用字符串b替换字符串str中所有出现的字符串a

mysql> select replace('hello','he','qqq')
    -> from dual;
+-----------------------------+
| replace('hello','he','qqq') |
+-----------------------------+
| qqqllo                      |
+-----------------------------+
1 row in set (0.11 sec)
1
2
3
4
5
6
7
8

upper(s)

将字符串s的所有字母转成大写字母

lower(s)

将字符串s的所有字母转成小写字母

mysql> select upper('hello'),lower('HELLO')
    -> from dual;
+----------------+----------------+
| upper('hello') | lower('HELLO') |
+----------------+----------------+
| HELLO          | hello          |
+----------------+----------------+
1 row in set (0.05 sec)
1
2
3
4
5
6
7
8

left(str,n)

返回字符串str最左边的n个字符

right(str,n)

返回字符串str最右边的n个字符

mysql> select left('hello',3),right('hello',3)
    -> from dual;
+-----------------+------------------+
| left('hello',3) | right('hello',3) |
+-----------------+------------------+
| hel             | llo              |
+-----------------+------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

ltrim(s)

去掉字符串s左侧的空格

rtrim(s)

去掉字符串s右侧的空格

trim(s)

去掉字符串s的首尾空格

mysql> select ltrim('  hello '),rtrim('  hello  '),trim('  hello  ')
    -> from dual;
+-------------------+--------------------+-------------------+
| ltrim('  hello ') | rtrim('  hello  ') | trim('  hello  ') |
+-------------------+--------------------+-------------------+
| hello             |   hello            | hello             |
+-------------------+--------------------+-------------------+
1
2
3
4
5
6
7

trim(s1 from s)

去掉字符串s首尾的字符s1

trim(leading s1 froms)

去掉字符串s开始处的s1

trim(trailing s1 froms)

去掉字符串s结尾处的s1