mysql字符串常用函数

字符串连接函数，参数是动态的

concat(str1,str2,…)

拼接str1,str2…在一起

mysql> select concat('huang','bao','kang');
+------------------------------+
| concat('huang','bao','kang') |
+------------------------------+
| huangbaokang                 |
+------------------------------+
1 row in set (0.00 sec)

insert(str,pos,len,newstr)

字符串str从pos位置开始的len字符串替换为newstr

mysql> select insert('I Love you',8,3,'zhanglulu');
+--------------------------------------+
| insert('I Love you',8,3,'zhanglulu') |
+--------------------------------------+
| I Love zhanglulu                     |
+--------------------------------------+
1 row in set (0.00 sec)

lower(str),upper(str)

小写转换字符串函数lower(str),大写转换字符串函数upper(str)

mysql> select lower('I Love zhanglulu');
+---------------------------+
| lower('I Love zhanglulu') |
+---------------------------+
| i love zhanglulu          |
+---------------------------+
1 row in set (0.00 sec)
mysql> select upper('I Love zhanglulu');
+---------------------------+
| upper('I Love zhanglulu') |
+---------------------------+
| I LOVE ZHANGLULU          |
+---------------------------+
1 row in set (0.00 sec)

length(str),char_length(str)

字符串长度函数length(str),char_length(str),其中length函数是以字节来统计的，char_length以字符来统计的，如果是单字节的字符，两者返回结果相同，如果utf8字符集下，一个汉字算三个字符，一个数字或字母算一个字符，其他编码下，一个汉字算两个字符，一个数字或者字母算一个字符。

mysql> select length("黄宝康");
+---------------------+
| length("黄宝康")    |
+---------------------+
|                   9 |
+---------------------+
1 row in set (0.00 sec)

mysql> select char_length("黄宝康");
+--------------------------+
| char_length("黄宝康")    |
+--------------------------+
|                        3 |
+--------------------------+
1 row in set (0.00 sec)

lpad(str,len,padstr),rpad(str,len,padstr)

字符串填补函数，分左右，lpad(str,len,padstr),rpad(str,len,padstr),用字符串 padstr对 str进行左/右边填补直至它的长度达到 len个字符长度，然后返回 str。如果 str的长度长于 len’，那么它将被截除到 len个字符。

mysql> select lpad('zhanglulu',20,'Love');
+-----------------------------+
| lpad('zhanglulu',20,'Love') |
+-----------------------------+
| LoveLoveLovzhanglulu        |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select rpad('zhanglulu',20,'Love');
+-----------------------------+
| rpad('zhanglulu',20,'Love') |
+-----------------------------+
| zhangluluLoveLoveLov        |
+-----------------------------+
1 row in set (0.00 sec)

trim(str)

trim(str)去除左右两边的空格

mysql> select trim('   I love you  ');
+-------------------------+
| trim('   I love you  ') |
+-------------------------+
| I love you              |
+-------------------------+
1 row in set (0.00 sec)

repeat(str,count)

repeat(str,count),返回重复count次str后的结果

mysql> select repeat('helloworld',3);
+--------------------------------+
| repeat('helloworld',3)         |
+--------------------------------+
| helloworldhelloworldhelloworld |
+--------------------------------+
1 row in set (0.00 sec)

replace(str,from_str,to_str)

字符串替换replace(str,from_str,to_str),用字符串to_str替换所有的字符串from_str

mysql> select replace('I hate you,hate you always! ','hate','love');
+-------------------------------------------------------+
| replace('I hate you,hate you always! ','hate','love') |
+-------------------------------------------------------+
| I love you,love you always!                           |
+-------------------------------------------------------+
1 row in set (0.00 sec)

substring(str,pos,len)

字符串截取substring(str,pos,len)，返回字符串str,从pos位置开始的len字符串，省略第三个参数，将截取到最后

mysql> select substring('huangbaokang',6);
+-----------------------------+
| substring('huangbaokang',6) |
+-----------------------------+
| baokang                     |
+-----------------------------+
1 row in set (0.00 sec)