字符串连接 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个参数为提取多长的字符串。