mysql字符串函数有:1、lower,将字符串参数值转换为全小写字母后返回;2、upper,将字符串参数值转换为全大写字母后返回;3、concat,将多个字符串参数首尾相连后返回;4、substr,从源字符串str中指定位置pos开始取。
mysql字符串函数有:
1、lower(column|str):将字符串参数值转换为全小写字母后返回
mysql> select lower('sql course');+---------------------+| lower('sql course') |+---------------------+| sql course |+---------------------+
2、upper(column|str):将字符串参数值转换为全大写字母后返回
mysql> select upper('use mysql');+--------------------+| upper('use mysql') |+--------------------+| use mysql |+--------------------+
3、concat(column|str1, column|str2,...):将多个字符串参数首尾相连后返回
mysql> select concat('my','s','ql');+-----------------------+| concat('my','s','ql') |+-----------------------+| mysql |+-----------------------+
如果有任何参数为null,则函数返回null
mysql> select concat('my',null,'ql');+------------------------+| concat('my',null,'ql') |+------------------------+| null |+------------------------+
如果参数是数字,则自动转换为字符串
mysql> select concat(14.3,'mysql');+----------------------+| concat(14.3,'mysql') |+----------------------+| 14.3mysql |+----------------------+
4、concat_ws(separator,str1,str2,...):将多个字符串参数以给定的分隔符separator首尾相连后返回
mysql> select concat_ws(';','first name','second name','last name');+-------------------------------------------------------+| concat_ws(';','first name','second name','last name') |+-------------------------------------------------------+| first name;second name;last name |+-------------------------------------------------------+
!!也就是函数圆括号里的第一个项目用来指定分隔符
5、substr(str,pos[,len]):从源字符串str中的指定位置pos开始取一个字串并返回
注意:
①len指定子串的长度,如果省略则一直取到字符串的末尾;len为负值表示从源字符串的尾部开始取起。
②函数substr()是函数substring()的同义词。
mysql> select substring('hello world',5);+----------------------------+| substring('hello world',5) |+----------------------------+| o world |+----------------------------+mysql> select substr('hello world',5,3);+---------------------------+| substr('hello world',5,3) |+---------------------------+| o w |+---------------------------+mysql> select substr('hello world',-5);+--------------------------+| substr('hello world',-5) |+--------------------------+| world |+--------------------------+
6、length(str):返回字符串的存储长度
mysql> select length('text'),length('你好');+----------------+------------------+| length('text') | length('你好') |+----------------+------------------+| 4 | 6 |+----------------+------------------+
注意:编码方式不同字符串的存储长度就不一样(‘你好’:utf8是6,gbk是4)
7、char_length(str):返回字符串中的字符个数
mysql> select char_length('text'),char_length('你好');+---------------------+-----------------------+| char_length('text') | char_length('你好') |+---------------------+-----------------------+| 4 | 2 |+---------------------+-----------------------+
8、instr(str, substr):从源字符串str中返回子串substr第一次出现的位置
mysql> select instr('foobarbar','bar');+--------------------------+| instr('foobarbar','bar') |+--------------------------+| 4 |+--------------------------+
9、lpad(str, len, padstr):在源字符串的左边填充给定的字符padstr到指定的长度len,返回填充后的字符串
mysql> select lpad('hi',5,'');+-------------------+| lpad('hi',5,'') |+-------------------+| ?hi |+-------------------+
10、rpad(str, len, padstr):在源字符串的右边填充给定的字符padstr到指定的长度len,返回填充后的字符串
mysql> select rpad('hi',6,'');+-------------------+| rpad('hi',6,'') |+-------------------+| hi |+-------------------+
11、trim([{both | leading | trailing} [remstr] from] str), trim([remstr from] str):
从源字符串str中去掉两端、前缀或后缀字符remstr并返回;
如果不指定remstr,则去掉str两端的空格;不指定both、leading、trailing ,则默认为 both。
mysql> select trim(' bar ');+-----------------+| trim(' bar ') |+-----------------+| bar |+-----------------+mysql> select trim(leading 'x' from 'xxxbarxxx');+------------------------------------+| trim(leading 'x' from 'xxxbarxxx') |+------------------------------------+| barxxx |+------------------------------------+mysql> select trim(both 'x' from 'xxxbarxxx');+---------------------------------+| trim(both 'x' from 'xxxbarxxx') |+---------------------------------+| bar |+---------------------------------+mysql> select trim(trailing 'xyz' from 'barxxyz');+-------------------------------------+| trim(trailing 'xyz' from 'barxxyz') |+-------------------------------------+| barx |+-------------------------------------+
12、replace(str, from_str, to_str):在源字符串str中查找所有的子串form_str(大小写敏感),找到后使用替代字符串to_str替换它。返回替换后的字符串
mysql> select replace('www.mysql.com','w','ww');+-----------------------------------+| replace('www.mysql.com','w','ww') |+-----------------------------------+| wwwwww.mysql.com |+-----------------------------------+
13、ltrim(str),rtrim(str):去掉字符串的左边或右边的空格(左对齐、右对齐)
mysql> select ltrim(' barbar ') rs1, rtrim(' barbar ') rs2;+-----------+-----------+| rs1 | rs2 |+-----------+-----------+| barbar | barbar |+-----------+-----------+
14、repeat(str, count):将字符串str重复count次后返回
mysql> select repeat('mysql',3);+-------------------+| repeat('mysql',3) |+-------------------+| mysqlmysqlmysql |+-------------------+
15、reverse(str):将字符串str反转后返回
mysql> select reverse('abcdef');+-------------------+| reverse('abcdef') |+-------------------+| fedcba |+-------------------+
16、char(n,... [using charset_name]):将每个参数n解释为整数(字符的编码),并返回每个整数对应的字符所构成的字符串(null值被忽略)。
mysql> select char(77,121,83,81,'76'),char(77,77.3,'77.3');+-------------------------+----------------------+| char(77,121,83,81,'76') | char(77,77.3,'77.3') |+-------------------------+----------------------+| mysql | mmm |+-------------------------+----------------------+
默认情况下,函数返回二进制字符串,若想返回针对特定字符集的字符串,使用using选项
mysql> select charset(char(0x65)), charset(char(0x65 using utf8));+---------------------+--------------------------------+| charset(char(0x65)) | charset(char(0x65 using utf8)) |+---------------------+--------------------------------+| binary | utf8 |+---------------------+--------------------------------+
17、format(x,d[,locale]):以格式‘#,###,###.##’格式化数字x
d指定小数位数
locale指定国家语言(默认的locale为en_us)
mysql> select format(12332.123456, 4),format(12332.2,0);+-------------------------+-------------------+
| format(12332.123456, 4) | format(12332.2,0) |
+-------------------------+-------------------+
| 12,332.1235 | 12,332 |
+-------------------------+-------------------+mysql> select format(12332.2,2,'de_de');+---------------------------+
| format(12332.2,2,'de_de') |
+---------------------------+
| 12.332,20 |
+---------------------------+
18、space(n):返回由n个空格构成的字符串
mysql> select space(3);+----------+| space(3) |+----------+| |+----------+
19、left(str, len):返回最左边的len长度的子串
mysql> select left('chinaitsoft',5);+-----------------------+| left('chinaitsoft',5) |+-----------------------+| china |+-----------------------+
20、right(str, len):返回最右边的len长度的子串
mysql> select right('chinaitsoft',5);+------------------------+| right('chinaitsoft',5) |+------------------------+| tsoft |+------------------------+
21、strcmp(expr1,expr2):如果两个字符串是一样的则返回0;如果第一个小于第二个则返回-1;否则返回1
mysql> select strcmp('text','text');+-----------------------+| strcmp('text','text') |+-----------------------+| 0 |+-----------------------+mysql> select strcmp('text', 'text2'),strcmp('text2', 'text');+-------------------------+-------------------------+| strcmp('text', 'text2') | strcmp('text2', 'text') |+-------------------------+-------------------------+| -1 | 1 |+-------------------------+-------------------------+
相关学习推荐:mysql视频教程
以上就是mysql字符串函数有哪些?的详细内容。