Mysql字符串函数

CONCAT()

将多个字符串连接成一个字符串，如有任何一个参数为NULL，则返回值为NULL
注意如果自变量中含有任一二进制字符串，则结果为一个二进制字符串
将整形字段转换成字符串返回或者希望日期格式以字符串形式返回都可以使用该方法
语法：CONCAT(str1,str2,...)

-- 122223
SELECT CONCAT(12,22,23)
-- NULL
SELECT CONCAT('11','22',NULL) 
-- 将表中的closing_time取时分秒和今天的日期拼接在一起返回
 SELECT
    DATE_FORMAT(closing_time,
      CONCAT(
          (SELECT DATE_FORMAT(NOW(), "%Y-%m-%d ")),"%H:%i:%s"
      )
    ) AS "TodayClosingTime"
  FROM
  work_shift_detail

CONCAT_WS()

代表含义为 CONCAT With Separator，是CONCAT()的特殊形式，第一个参数为分隔符，可以是一个字符串，也可以是其他参数
如果分隔符为NULL，则结果为NULL。函数会忽略任何分隔符参数后的NULL值，但不会忽略任何空字符串
语法：CONCAT_WS(separator,str1,str2,...)

-- love-me-once
SELECT CONCAT_WS('-','love','me','once')
-- love-once
SELECT CONCAT_WS('-','love',NULL,'once')
-- NULL
SELECT CONCAT_WS(NULL,'A','B')

GROUP_CONCAT()

• 该函数返回带有来自一个组的连接的非NULL值的字符串结果，用了group_concat后，select里如果使用了limit是不起作用的
• 通过使用 DISTINCT 可以排除重复值，使用 ORDER BY 对结果中的值进行排序
• SEPARATOR 分隔符，是一个字符串值，它被用于插入到结果值中。默认为逗号
• 可配合group by使用，连接字符长度受系统变量group_concat_max_len限制

语法：GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] [,col ...]] [SEPARATOR str_val])

-- 小明,小红,小兰
SELECT GROUP_CONCAT(name) FROM test;
-- 小明小红小兰
SELECT GROUP_CONCAT(name SEPARATOR '') FROM test;
-- 小兰小红小明
SELECT GROUP_CONCAT(DISTINCT name ORDER BY id DESC SEPARATOR '') FROM test;

INSERT()

字符串str从pos位置开始到len个字符的子串由字符串newstr代替
语法：INSERT(str,pos,len,newstr)

-- foreverLove
SELECT INSERT('foreverPain',8,4,'Love')

SUBSTR()、SUBSTRING()

从字符串str的起始位置po截取长度为len的子串，如果pos为0则返回空字符串，因为mysql数据库的下标是从1开始的
语法：SUBSTR(str FROM pos FOR len) == SUBSTR(str,pos,len)、SUBSTR(str FROM pos) == SUBSTR(str,pos)

-- I L
SELECT SUBSTR('I Love Java' FROM 1 FOR 3)
SELECT SUBSTR('I Love Java',1,3)
-- Love Java
SELECT SUBSTR('I Love Java' FROM 2)
SELECT SUBSTR('I Love Java',2)

SUBSTRING_INDEX()

返回从字符串str的第count个出现的分隔符delim之后的子串
如果count是正数，返回最后的分隔符到左边(从左边数) 的所有字符
如果count是负数，返回最后的分隔符到右边的所有字符(从右边数)
语法：SUBSTRING_INDEX(str,delim,count)

-- I
SELECT SUBSTRING_INDEX('I-Love-Java','-',1)
-- I-Love
SELECT SUBSTRING_INDEX('I-Love-Java','-',2)
-- Java
SELECT SUBSTRING_INDEX('I-Love-Java','-',-1)
-- Love-Java
SELECT SUBSTRING_INDEX('I-Love-Java','-',-2)

REPLACE()

将字符串的from_str的所有出现由字符串to_str代替
语法：REPLACE(str,from_str,to_str)

-- Life-is-hard
SELECT REPLACE('Life is hard',' ','-')

INSTR()

返回子串substr在字符串str中的第一个出现的位置。这与有2个参数形式的LOCATE()相同，除了参数被颠倒
语法：INSTR(str,substr)

-- foreverLove
-- 2
SELECT INSTR('My EveryThing','y')
-- 0
SELECT INSTR('My EveryThing','Z')

LOCATE()

返回子串substr在字符串str第一个出现的位置，从位置pos开始。找不到返回0
语法：LOCATE(substr,str)、LOCATE(substr,str,pos)

-- 2
SELECT LOCATE('y','My EveryThing')
-- 8
SELECT LOCATE('y','My EveryThing',3)

POSITION()

返回子串substr在字符串str第一个出现的位置，找不到返回0
语法：POSITION(substr IN str)

-- 2
SELECT POSITION('y' IN 'My EveryThing')
-- 1
SELECT POSITION('m' IN 'My EveryThing')

UCASE(str)、UPPER(str)

把字符串转换为大写

-- SILLY
SELECT UCASE('silly')

LCASE(str)、LOWER(str)

把字符串转换为小写

-- sillyme
SELECT LCASE('SillyMe')

MID()

用于从文本字段中提取字符，start必需规定开始位置(起始值是1)，
length要返回的字符数省略则返回剩余文本
语法：MID(column_name,start[,length])

-- 水月
SELECT MID('镜花水月',3,2)

LENGTH(str)、OCTET_LENGTH(str)

CHAR_LENGTH(str)、CHARACTER_LENGTH(str)

返回字符串str的长度，前两个函数是以字节为单位，多字节字符计为多个字节。后两个函数是以字符为单位，多字节字符算作单个字符。

-- 12
SELECT LENGTH('笑傲江湖')
-- 16
SELECT LENGTH('www.baidu666.com')
-- 4
SELECT CHAR_LENGTH('笑傲江湖')
-- 16
SELECT CHARACTER_LENGTH('www.baidu666.com')

ASCII()

返回字符串str的最左面字符的ASCII代码值。如果str是空字符串，返回0。如果str是NULL，返回NULL
语法：ASCII(str)

-- 97
SELECT ASCII('a')
-- 65
SELECT ASCII('Abc')

CHAR()

参数是一个数字序列，返回由参数对应的ASCII代码字符组成的一个字串，null值会被忽略
语法：CHAR(N,... [USING charset_name])

-- MySQL
SELECT CHAR(77,121,83,81,NULL,76,NULL)
-- MMM
SELECT CHAR(77,77.3,'77.3')

REPEAT()

返回由重复count次的字符串str组成的一个字符串，如果count <= 0，返回一个空字符串，如果str或count是NULL，返回NULL
语法：REPEAT(str,count)

-- mymy
SELECT REPEAT('my',2)
-- NULL
SELECT REPEAT('my',NULL)

TRIM()

返回字符串str，其所有remstr前缀或后缀被删除了。如果没有修饰符BOTH、LEADING或TRAILING给出，默认为both。如果remstr没被指定，空格将被删除
语法：TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)

-- 去除左右空格
SELECT TRIM(' trust ')
-- 去除左空格
SELECT TRIM(LEADING FROM ' trust ')

LTRIM()、RTRIM()

返回删除了其前置(后置)空格字符的字符串str
语法：LTRIM(str)、RTRIM(str)

-- 去除左空格
SELECT LTRIM(' trust ')
-- 去除右空格
SELECT RTRIM(' trust ')

LEFT()、RIGHT()

返回字符串str的最左面、右面len个字符
语法：LEFT(str,len)、RIGHT(str,len)

-- per
SELECT LEFT('persistent',3)
-- tent
SELECT RIGHT('persistent',4)

LPAD()、RPAD()

左面、右面用字符串padstr填补直到str是len个字符长
语法：LPAD(str,len,padstr)、RPAD(str,len,padstr)

-- upupupgive
SELECT LPAD('give',10,'up')
-- giveupupup
SELECT RPAD('give',10,'up')
-- giveu
SELECT RPAD('give',5,'up')

REVERSE()

颠倒字符串的字符顺序
语法：REVERSE(str)

-- yllis
SELECT REVERSE('silly')
-- 串符字
SELECT REVERSE('字符串')

CONV()

对数字N进行进制转换，并转换为字符串返回，任何参数为null时返回null。进制范围为2-36进制，当to_base是负数时n作为有符号数否则作为无符号数，conv以64位精度工作
语法：CONV(N,from_base,to_base)

-- 将16进制的a转换为2进制：1010
SELECT CONV("a",16,2)
-- 172
SELECT CONV("6e",18,8)
-- -H
SELECT CONV(-17,10,-18)
-- 40
SELECT CONV(10+"10"+'10'+0xa,10,10)

BIN(N)、OCT(N)、HEX(N)

返回N的二进制值的字符串表示，在此N是一个长整数(BIGINT)数字。如果N是NULL，返回NULL。
其中BIN(N)就等价于CONV(N,10,2)，OCT(N)等价于CONV(N,10,8)，HEX(N)等价于CONV(N,10,16)

-- 1010
SELECT BIN(10)
-- 12
SELECT OCT(10)
-- A
SELECT HEX(10)

FIND_IN_SET()

查询字段(strlist)中包含str的结果，返回符合的结果或者null，strlist通常是一个以逗号分隔的字符串列表。
如果str在strlist里面，返回一个正整数
如果str不在strlist里面或如果 strlist是空字符串，返回0
如果任何一个参数是NULL，返回NULL
如果第一个参数包含一个逗号，该函数将工作不正常
语法：FIND_IN_SET(str,strlist)

-- 结果：2
SELECT FIND_IN_SET('b', 'a,b,c,d')
-- 结果：0，与like的区别在于该函数是精准查询，b与列表的任何一项值都不相等，所以返回0
SELECT FIND_IN_SET('b', 'ab,bb,cc,dd')
-- NULL
SELECT FIND_IN_SET(NULL,'a,b,c');
-- 与in的区别在于in接收的参数为常量，而该函数可以使用字段
-- 比如有一张test表，有如下3条记录，查询颜色包含yellow的记录
-- id   color
-- 1    white,black,red
-- 2    blue,black,orange
-- 3    red,green,yellow
-- 空记录
SELECT * FROM test WHERE 'yellow' IN(color)
-- 第三条记录
SELECT * FROM test WHERE FIND_IN_SET('yellow',color)
-- 第二和第三条记录
SELECT * FROM test WHERE color IN('red,green,yellow','blue,black,orange')

SPACE()

返回由n个空格字符组成的一个字符串
语法：SPACE(N)

-- 6个空格
SELECT SPACE(6)