MySql FIND_IN_SET, SUBSTRING_INDEX, REGEXP正则匹配等字符串替换、截取函数应用

Mysql数据库提供了比较丰富的字符串函数，如上文“谈谈Mysql 字符串连接 CONCAT CONCAT_WS GROUP_CONCAT区别及使用场景”中提到的字符串连接函数，本文继续讲述Mysql剩余常用字符串函数的使用注意事项及使用场景。

1、字符串长度

1）CHAR_LENGTH(str)、CHARACTER_LENGTH(str)

Returns the length of the string str, measured in characters. A multibyte character counts as a single character. This means that for a string containing five 2-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.
返回字符数

2）LENGTH(str)

Returns the length of the string str, measured in bytes. A multibyte character counts as multiple bytes. This means that for a string containing five 2-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.
返回字节长度，不同编码返回结果不同。

select CHAR_LENGTH('中欧d');	->3
select LENGTH('中欧d');			->utf-8, 7
select LENGTH('中欧d');			->gbk, 5

2、字符串查找和替换

1）INSTR(str,substr)

Returns the position of the first occurrence of substring substr in string str. This is the same as the two-argument form of LOCATE(), except that the order of the arguments is reversed.
找到返回>0的值（从1开始计数），否则返回0
参数位置互换，等同LOCATE(substr,str)

2）LOCATE(substr,str),LOCATE(substr,str,pos)**

The first syntax returns the position of the first occurrence of substring substr in string str. The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str. Returns NULL if substr or str is NULL.
找到返回>0（从1开始计数），否则返回0
POSITION函数同LOCATE

SELECT INSTR('foobarbar', 'bar');		-> 4,第一次出现的位置
SELECT INSTR('xbar', 'foobar');		-> 0
SELECT LOCATE('bar', 'foobarbar');		-> 7
SELECT LOCATE('xbar', 'foobar');		-> 0

3）FIND_IN_SET(str,strlist)

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by , characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (,) character.
查找str是否在strlist以逗号连接的字符串中，str不能包含逗号，没找到返回0，找到返回>0值

SELECT FIND_IN_SET('b','a,b,c,d');		-> 2

使用场景：
在之前的博文：“谈谈企业信息系统tag标签数据库设计及基于多选组件bootstrap-select的实现”中曾提到FIND_IN_SET函数使用，如经销商渠道类型是tag标签，有：传统渠道、KA渠道、特通渠道等。查找传统渠道经销商，用FIND_IN_SET函数如下：

select * from sale_customers where FIND_IN_SET('传统渠道', tag_name) > 0

4）REGEXP：expr REGEXP pat, expr RLIKE pat

字符串正则匹配，匹配上返回1，否则返回0

mysql> SELECT 'Monty!' REGEXP '.*'; -> 1, 任意字符
mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line'; -> 1 \\*转义
mysql> SELECT 'a' REGEXP 'A', 'a' REGEXP BINARY 'A'; -> 1 0
mysql> SELECT 'a' REGEXP '^[a-d]'; -> 1, a-d字符开头

更多正则相关内容，请参考：Mysql参考手册5.7 13.5.2 Regular Expressions
因为Mysql字符串替换（replace）时不能使用正则，实际上正则使用率不是很高。

5）replace：replace(object,search,replace)

把object中出现search的全部替换为replace

mysql> update `base_goods` set `py`=replace(`py`,' ',''); //清除产品表中名称拼音字段中的空格  

3、字符串截取

1）SUBSTRING(str,pos),SUBSTRING(strFROMpos),SUBSTRING(str,pos,len),

SUBSTRING(str FROM pos FOR len)
从pos位置开始截取剩余所有字符串或截取长度为len的字符串。pos为负则从字符串右边开始计算位置，如-1则表示右边第1个。
SUBSTR() 同SUBSTRING

mysql> SELECT SUBSTRING('Quadratically',5); -> 'ratically',第5个开始余下所有字串
mysql> SELECT SUBSTRING('foobarbar' FROM 4); -> 'barbar'，第4个开始余下所有字串
mysql> SELECT SUBSTRING('Quadratically',5,6); -> 'ratica'，第5个开始，截取6个字符
mysql> SELECT SUBSTRING('Sakila', -3); -> 'ila'，倒数第3个开始，截取右侧所有字符
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2); -> 'ki'，倒数第4个开始，截取2个字符

2）SUBSTRING_INDEX(str,delim,count)

Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.
count > 0, 返回str按delim分割后左侧第count开始左侧字符串
count < 0, 返回str按delim分割后右侧第count开始右侧字符串。

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2); -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2); -> 'mysql.com'

由于mysql没有字符串分割函数，SUBSTRING_INDEX是一个可行的替代函数，如截取电话号码、省市区等是一个不错的选择。