concat将多高字符连接起来
格式:concat(S1,S2,S3,S4....SN)将S1到SN连接起来
mysql>select concat('abc','def','ghi');
+---------------------------+
| concat('abc','def','ghi') |
+---------------------------+
| abcdefghi |
+---------------------------+
1 row in set (0.01 sec)
mysql>select concat(1,2,3,4);
+-----------------+
| concat(1,2,3,4) |
+-----------------+
| 1234 |
+-----------------+
1 row in set (0.00 sec)
insert替换字符串
格式:insert(str,x,y,instr)将str中的第x字符到第y个字符替换成instr
mysql>select insert('abcdefghijklmn',2,5,'aaaa');
+-------------------------------------+
| insert('abcdefghijklmn',2,5,'aaaa') |
+-------------------------------------+
| aaaaaghijklmn |
+-------------------------------------+
1 row in set (0.00 sec)
mysql>select insert('abcdefghijklmn',2,5,'#');
+----------------------------------+
| insert('abcdefghijklmn',2,5,'#') |
+----------------------------------+
| a#ghijklmn |
+----------------------------------+
1 row in set (0.00 sec)
LOWER将字符转换为小写
格式:LOWER(str)将str转换为小写
mysql>select LOWER('SaBcds');
+-----------------+
| LOWER('SaBcds') |
+-----------------+
| sabcds |
+-----------------+
1 row in set (0.00 sec)
UPPER将字符转换为大写
格式:UPPER(str)将str转换为大写
mysql> select UPPER('SaBcds');
+-----------------+
| UPPER('SaBcds') |
+-----------------+
| SABCDS |
+-----------------+
1 row in set (0.00 sec)
LEFT显示左边两个字符
格式:LEFT(str,x)将str的最左边x个字符显示出来
mysql>select left('abcdefg',2);
+-------------------+
| left('abcdefg',2) |
+-------------------+
| ab |
+-------------------+
1 row in set (0.00 sec)
RIGHT显示又边两个字符
格式:RIGHT(str,x)将str的最右边x个字符显示出来
mysql>select right('abcdefg',2);
+--------------------+
| right('abcdefg',2) |
+--------------------+
| fg |
+--------------------+
1 row in set (0.00 sec)
LPAD字符填充
格式:LPAD(str,n,pad)用字符串pad对str最左边进行填充,直到长度为N个字符串,如果字符串长度大于N则截取N个字符串显示
mysql>select LPAD('abcdefg',20,'#');
+------------------------+
| LPAD('abcdefg',20,'#') |
+------------------------+
| #############abcdefg |
+------------------------+
1 row in set (0.00 sec)
mysql>select LPAD('abcdefg',2,'#');
+-----------------------+
| LPAD('abcdefg',2,'#') |
+-----------------------+
| ab |
+-----------------------+
1 row in set (0.00 sec
RPAD字符填充
格式:RPAD(str,n,pad)用字符串pad对str最又边进行填充,直到长度为N个字符串,如果字符串长度大于N则截取N个字符串显示
mysql>select RPAD('abcdefg',20,'#');
+------------------------+
| RPAD('abcdefg',20,'#') |
+------------------------+
| abcdefg############# |
+------------------------+
1 row in set (0.00 sec)
LTRIM忽略左边空格
格式:LTRIM(str)忽略str左边空格
mysql>select CONCAT('#',LTRIM(' abc '),'#');
+-----------------------------------------------------------------+
| CONCAT('#',LTRIM(' abc '),'#') |
+-----------------------------------------------------------------+
| #abc # |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)
RTRIM忽略左边空格
格式:RTRIM(str)忽略str右边空格
mysql> select CONCAT('#',rTRIM(' abc '),'#');
+-----------------------------------------------------------------+
| CONCAT('#',rTRIM(' abc '),'#') |
+-----------------------------------------------------------------+
| # abc# |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)
REPEAT重复字符串
格式:repeat(str,x)将str重复X次
mysql>select repeat('abc|',20);
+----------------------------------------------------------------------------------+
| repeat('abc|',20) |
+----------------------------------------------------------------------------------+
|abc|abc|abc|abc|abc|abc|abc|abc|abc|abc|abc|abc|abc|abc|abc|abc|abc|abc|abc|abc||
+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)
REPLACE替换字符
格式:repeat(str1,a,b)使用b替换掉str中的a
mysql>select replace('abcdefgaaacdbssdfbaljfklsaf','a','#');
+------------------------------------------------+
|replace('abcdefgaaacdbssdfbaljfklsaf','a','#') |
+------------------------------------------------+
| #bcdefg###cdbssdfb#ljfkls#f |
+------------------------------------------------+
1 row in set (0.00 sec)
STRCMP比较两个字符串的ASCII码大小
格式:STRCMP(s1,s2)比较两个字符串的ASCII码大小,如果s1>s2则返回1,如果相等则返回0反之返回-1
mysql>select STRCMP('a','b'),STRCMP('b','a'),STRCMP('b','b');
+-----------------+-----------------+-----------------+
| STRCMP('a','b') | STRCMP('b','a') |STRCMP('b','b') |
+-----------------+-----------------+-----------------+
| -1 | 1 | 0 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)
TRIM去掉字符串头与尾的空格
格式:TRIM(STR)去掉str左右的空格
mysql>select concat('|',TRIM(' a b '),'|');
+-----------------------------------------+
| concat('|',TRIM(' a b '),'|') |
+-----------------------------------------+
| |a b| |
+-----------------------------------------+
1 row in set (0.00 sec)
SUBSTRING显示指定的字符
格式:substring(str,x,y)显示str中从第x个字符开始往后数y个字符
mysql>select substring('abcdefghijkl',2,5);
+-------------------------------+
| substring('abcdefghijkl',2,5) |
+-------------------------------+
| bcdef |
+-------------------------------+
1 row in set (0.00 sec)