ascii(s)
返回字符串S中的第一个字符的ASCII码值
mysql> select ascii('a') from dual;
+------------+
| ascii('a') |
+------------+
| 97 |
+------------+
1 row in set (0.00 sec)
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)
length(s)
返回字符串s的字节数,和字符集有关
mysql> select length('hello'),length('我们')
-> from dual;
+-----------------+----------------+
| length('hello') | length('我们') |
+-----------------+----------------+
| 5 | 4 |
+-----------------+----------------+
1 row in set (0.05 sec)
concat(s1,s2,…,sn)
连接s1,s2,…,sn为一个字符串
mysql> select concat( '你好','黄壮') from dual;
+------------------------+
| concat( '你好','黄壮') |
+------------------------+
| 你好黄壮 |
+------------------------+
1 row in set (0.12 sec)
concat_ws(x,s1,s2,…,sn)
也是拼接字符串,但是每个字符串之间要加上x
mysql> select concat_ws('-','hello','China') as "拼接后效果";
+-------------+
| 拼接后效果 |
+-------------+
| hello-China |
+-------------+
1 row in set (0.10 sec)
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)
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)
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)
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)
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 |
+-------------------+--------------------+-------------------+
trim(s1 from s)
去掉字符串s首尾的字符s1
trim(leading s1 froms)
去掉字符串s开始处的s1
trim(trailing s1 froms)
去掉字符串s结尾处的s1