MySQL中常见字符串操作函数

字符串连接 concat

mysql> select concat ('who',' ','am',' ','I?')    -> ;+----------------------------------+| concat ('who',' ','am',' ','I?') |+----------------------------------+| who am I?                        |+----------------------------------+1 row in set (0.00 sec)

ORACLE中用 || ，sql server 中用+

长度函数 length

mysql> select length('abc');+---------------+| length('abc') |+---------------+|             3 |+---------------+1 row in set (0.00 sec)

定位函数 position、locate

position

mysql> select position('3' in '0123');+-------------------------+| position('3' in '0123') |+-------------------------+|                       4 |+-------------------------+1 row in set (0.00 sec)

locate

mysql> select locate('3' ,'0123',3);+-----------------------+| locate('3' ,'0123',3) |+-----------------------+|                     4 |+-----------------------+1 row in set (0.00 sec)mysql> select locate('3' ,'0123',4);+-----------------------+| locate('3' ,'0123',4) |+-----------------------+|                     4 |+-----------------------+1 row in set (0.00 sec)mysql> select locate('3' ,'0123',5);+-----------------------+| locate('3' ,'0123',5) |+-----------------------+|                     0 |+-----------------------+1 row in set (0.00 sec)

oracle用instr()和locat()来实现上面两个功能，sql server 用charindex()来实现

case功能：strcmp(),regexp,like

类似oracle中的case功能，MySQL中有以下函数

strcmp()

select strcmp(‘123’,’abc’)
比较两个字符串的次序：

前者排序在后者之前返回-1

两个字符串相同返回0

后者排序在前者之前返回1

like

如果like命中则返回1，不命中返回0

mysql> select a like '%bb' from (select 'abb' a) a    -> ;+--------------+| a like '%bb' |+--------------+|            1 |+--------------+1 row in set (0.01 sec)mysql> select a like '%bb' from (select 'abbc' a) a;+--------------+| a like '%bb' |+--------------+|            0 |+--------------+1 row in set (0.00 sec)

regexp

格式化函数，如果是预期格式返回1，不是是0

mysql> select a regexp '.{a}-.{a}-.{a}-.{a}' from (select 'a-b-b-c' a) a;+--------------------------------+| a regexp '.{a}-.{a}-.{a}-.{a}' |+--------------------------------+|                              0 |+--------------------------------+1 row in set (0.01 sec)mysql> select a regexp '.{1}-.{1}-.{1}-.{1}' from (select 'a-b-b-c' a) a;+--------------------------------+| a regexp '.{1}-.{1}-.{1}-.{1}' |+--------------------------------+|                              1 |+--------------------------------+1 row in set (0.00 sec)

ascii 函数

这个函数用来看单个字符的排序数字

mysql> select ascii('1');+------------+| ascii('1') |+------------+|         49 |+------------+1 row in set (0.00 sec)mysql> select ascii('2');+------------+| ascii('2') |+------------+|         50 |+------------+1 row in set (0.00 sec)mysql> select ascii('a');+------------+| ascii('a') |+------------+|         97 |+------------+1 row in set (0.00 sec)mysql> select ascii('A');+------------+| ascii('A') |+------------+|         65 |+------------+1 row in set (0.00 sec)

insert 函数

有4个参数，分别表示：原始字符串、字符串操作开始位置、要替换字符串的个数、插入字符串

mysql> select insert('adef',2,0,'bc') string;+--------+| string |+--------+| abcdef |+--------+1 row in set (0.00 sec)mysql> select insert('adef',2,1,'bc') string;+--------+| string |+--------+| abcef  |+--------+1 row in set (0.00 sec)mysql> select insert('adef',2,2,'bc') string;+--------+| string |+--------+| abcf   |+--------+1 row in set (0.00 sec)

oracle用replace函数，sql server 用replace和stuff函数。

substring() 字符串截取函数

这个函数应该比较常见，oracle中用substr()，sql server总用的也是substring

mysql> select substring('abcd123efg',5,3);+-----------------------------+| substring('abcd123efg',5,3) |+-----------------------------+| 123                         |+-----------------------------+1 row in set (0.00 sec)

上面例子把字符串abcd123efg中的123提取出来，第一个参数为原始字符串，第2个参数为从哪个位置开始提取，第3个参数为提取多长的字符串。