功能用法简单例子一览表
| 函数 | 功能 | 用法 | 例子 |
| left() | 从字符串左边为边界返回相应长度的子字符串 | left(str, length) |
mysql> select left('vssf',3);
+----------------+
| left('vssf',3) |
+----------------+
| vss |
+----------------+
1 row in set (0.00 sec)
|
| right() | 从字符串右边为边界返回相应长度的子字符串 | rigth(str,length) |
mysql> select right('vssf',1);
+-----------------+
| right('vssf',1) |
+-----------------+
| f |
+-----------------+
1 row in set (0.00 sec)
|
| substring() | 根据起始位置和长度返回相应的子字符串 | substring(str,pos,length) |
mysql> select substring('hello',1,1);
+------------------------+
| substring('hello',1,1) |
+------------------------+
| h |
+------------------------+
1 row in set (0.00 sec)
mysql> select substring('hello',-4,1);
+-------------------------+
| substring('hello',-4,1) |
+-------------------------+
| e |
+-------------------------+
1 row in set (0.00 sec)
|
| substring_index() |
以某个字符(delim)为选取点，截取到该字符的所有字符.
count为正数时从左到改字符，为负数时从该字符到右。
如果找不到delim,则返回整个字符串
|
substring_index(str,delim,count) |
mysql> select substring_index('hello.com.index','.',1);
+------------------------------------------+
| substring_index('hello.com.index','.',1) |
+------------------------------------------+
| hello |
+------------------------------------------+
1 row in set (0.00 sec)
|
| ASCII（） | 返回字符串str最左边字符的ascii码值,如果是空字符串，则返回0，如果是null，则返回null。输入是整型，也当做字符串处理 | ascii(str) |
mysql> select ascii('2');select ascii(2);
+------------+
| ascii('2') |
+------------+
| 50 |
+------------+
1 row in set (0.00 sec)
+----------+
| ascii(2) |
+----------+
| 50 |
+----------+
1 row in set (0.00 sec)
|
| length() | 计算字符串长度 | length(str) |
mysql> select length(user());
+----------------+
| length(user()) |
+----------------+
| 14 |
+----------------+
1 row in set (0.00 sec)
|
| locate（） | 返回子串substr在字符串str第一个出现的位置，从位置pos开始。如果substr不是在str里面，返回0 | LOCATE(substr,str,pos) |
mysql> select locate('123','141234',2);
+--------------------------+
| locate('123','141234',2) |
+--------------------------+
| 3 |
+--------------------------+
1 row in set (0.00 sec)
|
| instr() | 返回子串substr在字符串str中的第一个出现的位置。这与有2个参数形式的LOCATE()相同，除了参数被颠倒 | INSTR(str,substr) |
mysql> select instr('12345','123');
+----------------------+
| instr('12345','123') |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.00 sec)
|
| lpad() | 返回字符串str，左面用字符串padstr填补直到str是len个字符长 | LPAD (str,len,padstr) |
mysql> select LPAD('123',10,'456');
+----------------------+
| LPAD('123',10,'456') |
+----------------------+
| 4564564123 |
+----------------------+
1 row in set (0.00 sec)
|
| ltrim() | 返回删除了其前置空格字符的字符串str | LTRIM(str) |
mysql> select ltrim(' 121');
+-----------------+
| ltrim(' 121') |
+-----------------+
| 121 |
+-----------------+
1 row in set (0.00 sec)
|
| rtrim() | 返回删除了其拖后空格字符的字符串str | RTRIM(str) |
mysql> select rtrim('121 ');
+----------------+
| rtrim('121 ') |
+----------------+
| 121 |
+----------------+
1 row in set (0.00 sec)
|
| ucase() | 返回字符串str，根据当前字符集映射(缺省是ISO-8859-1 Latin1)把所有的字符改变成大写。该函数对多字节是可靠的 | UCASE(str) |
mysql> select UCASE('sae');
+--------------+
| UCASE('sae') |
+--------------+
| SAE |
+--------------+
1 row in set (0.00 sec)
|
| lcase() | 返回字符串str，根据当前字符集映射(缺省是ISO-8859-1 Latin1)把所有的字符改变成小写。该函数对多字节是可靠的。 | LCASE(str) |
mysql> select LCASE('sS');
+-------------+
| LCASE('sS') |
+-------------+
| ss |
+-------------+
1 row in set (0.00 sec)
|
| make_set() | 返回一个集合 (包含由“,”字符分隔的子串组成的一个字符串)，由相应的位在bits集合中的的字符串组成。str1对应于位0，str2对应位1，等等。在str1, str2, ...中的NULL串不添加到结果中。 | MAKE_SET(bits,str1,str2,...) |
mysql> select MAKE_SET(1|2,user,pass) from ctf;
+-------------------------+
| MAKE_SET(1|2,user,pass) |
+-------------------------+
| 1admin,11 |
| 4admin
,pass |
| admin123,123 |
| admin,test
|
+-------------------------+
4 rows in set (0.00 sec)
|
| reverse() | 返回颠倒字符顺序的字符串str | REVERSE(str) |
mysql> select REVERSE('123');
+----------------+
| REVERSE('123') |
+----------------+
| 321 |
+----------------+
1 row in set (0.00 sec)
|
| space() | 返回由N个空格字符组成的一个字符串 | SPACE(N) |
mysql> select concat(1,SPACE(2),3);
+----------------------+
| concat(1,SPACE(2),3) |
+----------------------+
| 1 3 |
+----------------------+
1 row in set (0.00 sec)
|
| soundex | 返回str的一个同音字符串。听起来“大致相同”的2个字符串应该有相同的同音字符串。一个“标准”的同音字符串长是4个字符，但是SOUNDEX()函数返回一个任意长的字符串。你可以在结果上使用SUBSTRING()得到一个“标准”的 同音串。所有非数字字母字符在给定的字符串中被忽略。所有在A-Z之外的字符国际字母被当作元音。 | SOUNDEX(str) |
mysql> select soundex('hello');
+------------------+
| soundex('hello') |
+------------------+
| H400 |
+------------------+
1 row in set (0.00 sec)
|
| trim() | 返回字符串str，其所有remstr前缀或后缀被删除了。如果没有修饰符BOTH、LEADING或TRAILING给出，BOTH被假定。如果remstr没被指定，空格被删除。 | TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str) |
mysql> select trim(' dsf ');
+---------------+
| trim(' dsf ') |
+---------------+
| dsf |
+---------------+
1 row in set (0.00 sec)
mysql> select trim(leading 'x' from 'xxxbarxxx');
+------------------------------------+
| trim(leading 'x' from 'xxxbarxxx') |
+------------------------------------+
| barxxx |
+------------------------------------+
1 row in set (0.00 sec)
mysql> select trim(trailing 'x' from 'xxxbarxxx');
+-------------------------------------+
| trim(trailing 'x' from 'xxxbarxxx') |
+-------------------------------------+
| xxxbar |
+-------------------------------------+
1 row in set (0.00 sec)
|
| relace() | 返回字符串str，其字符串from_str的所有出现由字符串to_str代替 | REPLACE(str,from_str,to_str) |
mysql> select replace('www.ok.com','w','q');
+-------------------------------+
| replace('www.ok.com','w','q') |
+-------------------------------+
| qqq.ok.com |
+-------------------------------+
1 row in set (0.00 sec)
|
| repeat() | 返回由重复countTimes次的字符串str组成的一个字符串。如果count <= 0，返回一个空字符串。如果str或count是NULL，返回NULL。 | REPEAT(str,count) |
mysql> select repeat('223',2);
+-----------------+
| repeat('223',2) |
+-----------------+
| 223223 |
+-----------------+
1 row in set (0.00 sec)
|
| insert() | 返回字符串str，在位置pos起始的子串且len个字符长得子串由字符串newstr代替 | INSERT(str,pos,len,newstr) |
mysql> select insert('dsfhdsa',3,4,'ww');
+----------------------------+
| insert('dsfhdsa',3,4,'ww') |
+----------------------------+
| dswwa |
+----------------------------+
1 row in set (0.00 sec)
|
| elt() | 如果N= 1，返回str1，如果N= 2，返回str2，等等。如果N小于1或大于参数个数，返回NULL。ELT()是FIELD()反运算 | ELT(N,str1,str2,str3,...) |
mysql> select elt(1,user(),version());
+-------------------------+
| elt(1,user(),version()) |
+-------------------------+
| root@localhost |
+-------------------------+
1 row in set (0.00 sec)
mysql> select elt(2,user(),version());
+-------------------------+
| elt(2,user(),version()) |
+-------------------------+
| 5.5.47 |
+-------------------------+
1 row in set (0.00 sec)
|
| field() | 返回str在str1, str2, str3, ...清单的索引。如果str没找到，返回0。FIELD()是ELT()反运算 | FIELD(str,str1,str2,str3,...) |
mysql> select field('v','he','hekl');
+------------------------+
| field('v','he','hekl') |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.00 sec)
|
| mid() | 根据起始位置和长度返回相应的子字符串，与substring一样的用法 | MID(ColumnName, Start [, Length]) |
mysql> select mid('sfsdf',2,1);
+------------------+
| mid('sfsdf',2,1) |
+------------------+
| f |
+------------------+
1 row in set (0.00 sec)
mysql> select mid('sfsdf',2);
+----------------+
| mid('sfsdf',2) |
+----------------+
| fsdf |
+----------------+
1 row in set (0.00 sec)
|
| concat() | 连接参数str1,str2等产生字符串,若参数中包含有NULL,则直接返回null | CONCAT(str1,str2,…) |
mysql> select concat('1','2');
+-----------------+
| concat('1','2') |
+-----------------+
| 12 |
+-----------------+
1 row in set (0.00 sec)
mysql> select concat('1','2',null);
+----------------------+
| concat('1','2',null) |
+----------------------+
| NULL |
+----------------------+
1 row in set (0.00 sec)
|
| concat_wd() | 连接参数产生字符串，可自定义分隔符，参数中有null,也不会返回null，正常返回其他字符串连接的结果 | CONCAT_WS(separator,str1,str2,...) |
mysql> select concat_ws(',','2','3',null);
+-----------------------------+
| concat_ws(',','2','3',null) |
+-----------------------------+
| 2,3 |
+-----------------------------+
1 row in set (0.00 sec)
|
| group_concat() | 连接参数产生字符串，可定义分隔符，可去重，可排序 |
group_concat([DISTINCT] 要连接的
字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
|
mysql> select group_concat(user,pass) from ctf;
+---------------------------------------------+
| group_concat(user,pass) |
+---------------------------------------------+
| 1admin11,4admin
pass,admin123123,admintest
|
+---------------------------------------------+
1 row in set (0.00 sec)
|