MySQL字符串函数总结

来源：互联网发布：pdf编辑软件免费版编辑：程序博客网时间：2024/05/01 09:46

(一)字符串函数的作用就是出来数据库中的字符串数据。

CREATE TABLE `test` (  `id` int(11) NOT NULL,  `first_name` varchar(255) DEFAULT NULL,  `last_name` varchar(255) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `test` VALUES ('201701', '凯', '耐');INSERT INTO `test` VALUES ('201702', '张', '三峰');INSERT INTO `test` VALUES ('201703', '欧阳', '小龙');INSERT INTO `test` VALUES ('201704', 'james', 'king');

mysql> select*from test;+--------+------------+-----------+| id     | first_name | last_name |+--------+------------+-----------+| 201701 | 凯         | 耐        || 201702 | 张         | 三峰      || 201703 | 欧阳       | 小龙      || 201704 | james      | king      |+--------+------------+-----------+

1.使用char_length(string)函数计算字符总数

#1查询first_name字段的字符个数mysql> select char_length(first_name) from test;+-------------------------+| char_length(first_name) |+-------------------------+|                       1 ||                       1 ||                       2 |+-------------------------+3 rows in set#2使用嵌套先连接俩字段，再计算字符个数mysql> select  char_length(concat(first_name,last_name)) from test;+-------------------------------------------+| char_length(concat(first_name,last_name)) |+-------------------------------------------+|                                         2 ||                                         3 ||                                         4 |+-------------------------------------------+3 rows in set

2.使用length(str)计算字符串长度

mysql>  select length(first_name )from test;+---------------------+| length(first_name ) |+---------------------+|                   3 ||                   3 ||                   6 ||                   5 |+---------------------+4 rows in set主：1.一次只能计算一个字段中的字符长度   2.在unicaode编码一个汉字占3个字节，一个数字或一个字母占一个字节。

3.使用concat(str1,str2..strn)合并字符串

mysql> select concat(first_name,last_name)as fullname from test;+-----------+| fullname  |+-----------+| 凯耐      || 张三峰    || 欧阳小龙  || jamesking |+-----------+4 rows in set

4.使用concat_ws()分隔字符串

mysql> select concat_ws('-',id,first_name,last_name) as fullLink from test;+-------------------+| fullLink          |+-------------------+| 201701-凯-耐      || 201702-张-三峰    || 201703-欧阳-小龙  || 201704-james-king |+-------------------+4 rows in set注：分隔符号可以是任意符号。

5.使用insert(string1,index,length,string2)替换字符串

mysql>  select insert('12345',2,2,'abc') as result;+--------+| result |+--------+| 1abc45 |+--------+1 row in set

6.使用lower(str)、lcase(str)将字符串转为小写

mysql> select lower('HELLO WORLD');+----------------------+| lower('HELLO WORLD') |+----------------------+| hello world          |+----------------------+1 row in setmysql> select lcase('HELLO WORLD');+----------------------+| lcase('HELLO WORLD') |+----------------------+| hello world          |+----------------------+1 row in set

7.使用upper(str)、ucase(str)

mysql> select upper('hello world');+----------------------+| upper('hello world') |+----------------------+| HELLO WORLD          |+----------------------+1 row in setmysql> select ucase('hello world');+----------------------+| ucase('hello world') |+----------------------+| HELLO WORLD          |+----------------------+1 row in set

8.left(s,n)、right(s,n)获取指定长度的字符串

mysql> select left('JamesFans',5);+---------------------+| left('JamesFans',5) |+---------------------+| James               |+---------------------+1 row in setmysql> select right('JamesFans',4);+----------------------+| right('JamesFans',4) |+----------------------+| Fans                 |+----------------------+1 row in set

9.使用左右填充字符串函数lpad(s1,len,s2)、rpad(s1,len,s2)

mysql> select lpad('love',7,'you'),rpad('love',5,'I');+----------------------+--------------------+| lpad('love',7,'you') | rpad('love',5,'I') |+----------------------+--------------------+| youlove              | loveI              |+----------------------+--------------------+1 row in set

10.使用ltrim()、rtrim()、trim()删除左空格、右空格、和左右空格。

#计算字符长度mysql> select   length('  h e l l o   ');+--------------------------+| length('  h e l l o   ') |+--------------------------+|                       14 |+--------------------------+1 row in set#1.删除左空格，再计算字符长度mysql> select length(ltrim('  h e l l o   '));+---------------------------------+| length(ltrim('  h e l l o   ')) |+---------------------------------+|                              12 |+---------------------------------+1 row in set#2删除右空格，再计算字符长度mysql> select length(rtrim('  h e l l o   '));+---------------------------------+| length(rtrim('  h e l l o   ')) |+---------------------------------+|                              11 |+---------------------------------+1 row in set#3删除所有空格，再计算字符长度mysql> select length(trim('  h e l l o   '));+--------------------------------+| length(trim('  h e l l o   ')) |+--------------------------------+|                              9 |+--------------------------------+1 row in setmysql> select  trim('  h e l l o   ');+------------------------+| trim('  h e l l o   ') |+------------------------+| h e l l o              |+------------------------+1 row in set注：中间空格没法删！

11.使用trim(str1 from str2)删除指定长度字符串

mysql> select trim('ab' from 'abcdefg');+---------------------------+| trim('ab' from 'abcdefg') |+---------------------------+| cdefg                     |+---------------------------+1 row in set注：从字符串abcdefg删除ab

12.使用repeat(s,n)重复生成字符串

mysql> select repeat('l23',2),repeat('l23',null),repeat(null,2);+-----------------+--------------------+----------------+| repeat('l23',2) | repeat('l23',null) | repeat(null,2) |+-----------------+--------------------+----------------+| l23l23          | NULL               | NULL           |+-----------------+--------------------+----------------+1 row in set

13.使用空格函数space(n)和替换函数replace(s,s1,s2)

mysql> select concat('My',space(10),'love');+-------------------------------+| concat('My',space(10),'love') |+-------------------------------+| My          love              |+-------------------------------+1 row in setmysql> select replace('III love you','I','fuck');+------------------------------------+| replace('III love you','I','fuck') |+------------------------------------+| fuckfuckfuck love you              |+------------------------------------+1 row in set

14.使用函数strcmp(s1,s2)比较字符串长度

mysql> select strcmp('123','1234'),strcmp('1234','123'),strcmp('123','123');+----------------------+----------------------+---------------------+| strcmp('123','1234') | strcmp('1234','123') | strcmp('123','123') |+----------------------+----------------------+---------------------+|                   -1 |                    1 |                   0 |+----------------------+----------------------+---------------------+1 row in set

15.使用substring(s,n,len)和mid(s,n,len)获取子字符串

mysql> select substring('abcdecf',3,2)as colmun1,substring('abcdecf',-1,3)as colmun2,substring('abcdecf',3)as colmun3;+---------+---------+---------+| colmun1 | colmun2 | colmun3 |+---------+---------+---------+| cd      | f       | cdecf   |+---------+---------+---------+1 row in set

16.使用position(str1 in str)匹配子串开始的位置。

mysql>  select position('cc' in'aabbcc');+---------------------------+| position('cc' in'aabbcc') |+---------------------------+|                         5 |+---------------------------+1 row in set

17.使用reverse(str)将字符串倒序

mysql> select reverse('123456789')as result;+-----------+| result    |+-----------+| 987654321 |+-----------+

18.使用elt(n,str1,str2..strn)获取指定位置的字符串

mysql> select elt(2,'a','ab','abc');+-----------------------+| elt(2,'a','ab','abc') |+-----------------------+| ab                    |+-----------------------+1 row in setmysql> select elt(4,'a','ab','abc');+-----------------------+| elt(4,'a','ab','abc') |+-----------------------+| NULL                  |+-----------------------+1 row in set

阅读全文

0 0