MySQL字符串函数

官方文档：String Functions

Name	Description
ASCII()	Return numeric value of left-most character
BIN()	Return a string containing binary representation of a number
BIT_LENGTH()	Return length of argument in bits
CHAR()	Return the character for each integer passed
CHAR_LENGTH()	Return number of characters in argument
CHARACTER_LENGTH()	Synonym for CHAR_LENGTH()
CONCAT()	Return concatenated string
CONCAT_WS()	Return concatenate with separator
ELT()	Return string at index number
EXPORT_SET()	Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string
FIELD()	Return the index (position) of the first argument in the subsequent arguments
FIND_IN_SET()	Return the index position of the first argument within the second argument
FORMAT()	Return a number formatted to specified number of decimal places
FROM_BASE64()	Decode to a base-64 string and return result
HEX()	Return a hexadecimal representation of a decimal or string value
INSERT()	Insert a substring at the specified position up to the specified number of characters
INSTR()	Return the index of the first occurrence of substring
LCASE()	Synonym for LOWER()
LEFT()	Return the leftmost number of characters as specified
LENGTH()	Return the length of a string in bytes
LIKE	Simple pattern matching
LOAD_FILE()	Load the named file
LOCATE()	Return the position of the first occurrence of substring
LOWER()	Return the argument in lowercase
LPAD()	Return the string argument, left-padded with the specified string
LTRIM()	Remove leading spaces
MAKE_SET()	Return a set of comma-separated strings that have the corresponding bit in bits set
MATCH	Perform full-text search
MID()	Return a substring starting from the specified position
NOT LIKE	Negation of simple pattern matching
NOT REGEXP	Negation of REGEXP
OCT()	Return a string containing octal representation of a number
OCTET_LENGTH()	Synonym for LENGTH()
ORD()	Return character code for leftmost character of the argument
POSITION()	Synonym for LOCATE()
QUOTE()	Escape the argument for use in an SQL statement
REGEXP	Pattern matching using regular expressions
REPEAT()	Repeat a string the specified number of times
REPLACE()	Replace occurrences of a specified string
REVERSE()	Reverse the characters in a string
RIGHT()	Return the specified rightmost number of characters
RLIKE	Synonym for REGEXP
RPAD()	Append string the specified number of times
RTRIM()	Remove trailing spaces
SOUNDEX()	Return a soundex string
SOUNDS LIKE	Compare sounds
SPACE()	Return a string of the specified number of spaces
STRCMP()	Compare two strings
SUBSTR()	Return the substring as specified
SUBSTRING()	Return the substring as specified
SUBSTRING_INDEX()	Return a substring from a string before the specified number of occurrences of the delimiter
TO_BASE64()	Return the argument converted to a base-64 string
TRIM()	Remove leading and trailing spaces
UCASE()	Synonym for UPPER()
UNHEX()	Return a string containing hex representation of a number
UPPER()	Convert to uppercase
WEIGHT_STRING()	Return the weight string for a string

注意：

• 字符串的下标从1开始；
• 如果函数要求的是整数参数，那么传入的非整数参数将会被自动转为最接近的整数。

1.ASCII

ASCII(str)

返回字符串str的最左面字符的ASCII代码值。如果str是空字符串，返回0；如果str是NULL，返回NULL。

2.BIN

BIN(N)

返回N的二进制字符串表示，在此N是一个长整数(BIGINT)数字，这等价于CONV(N,10,2)。如果N是NULL，返回NULL。 

3.BIT_LENTGH/LENGTH/OCTET_LENGTH/CHAR_LENGTH/CHARACTER_LENGTH

BITLENGTH(str)：位数

LENGTH(str)/OCTET_LENGTH(str)：字节数

CHAR_LENGTH(str)/CHARACTER_LENGTH(str)：字符数

4.CHAR

CHAR(N,... [USING charset_name])
CHAR()将参数解释为整数并且返回由这些整数在字符集里所代表字符组成的一个字符串。NULL值被跳过。

mysql> select CHAR(77,121,83,81,'76');
-> 'MySQL'
mysql> select CHAR(77,77.3,'77.3');
-> 'MMM'

5.CONCAT/CONCAT_WS

• CONCAT(str1,str2,...) 

返回来自于参数连结的字符串。如果任何参数是NULL，返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式。

mysql> select CONCAT('My', 'S', 'QL');
-> 'MySQL'
mysql> select CONCAT('My', NULL, 'QL');
-> NULL
mysql> select CONCAT(14.3);
-> '14.3'

• CONCAT_WS(separator,str1,str2,...)

CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。不会跳过空字符串参数，但会跳过NULL参数。

SELECT CONCAT_WS(";",id,title) FROM my_table LIMIT 100;
SELECT CONCAT_WS(";",'aa','bb') FROM my_table

　

6.ELT

ELT(N,str1,str2,str3,...) 
如果N= 1，返回str1，如果N= 2，返回str2，等等。如果N小于1或大于参数个数，返回NULL。ELT()是FIELD()反运算。 

7.EXPORT_SET

EXPORT_SET(bits,on,off,[separator,[number_of_bits]) 

返回一个字符串，在这里对于在“bits”中设定每一位，你得到一个“on”字符串，并且对于每个复位(reset)的位，你得到一个“off”字符串。每个字符串用“separator”分隔(缺省“,”)，并且只有“bits”的“number_of_bits” (缺省64)位被使用（从低位向高位）。

mysql> SELECT EXPORT_SET(5,'Y','N',',',4);
        -> 'Y,N,Y,N'
mysql> SELECT EXPORT_SET(6,'1','0',',',10);
        -> '0,1,1,0,0,0,0,0,0,0'

　　

8.FIELD

FIELD(str,str1,str2,str3,...) 
返回str在str1, str2, str3, ...清单的索引。如果str没找到，返回0。FIELD()是ELT()反运算。

9.FIND_IN_SET

FIND_IN_SET(str,strlist) 
如果字符串str在由N子串组成的表strlist之中，返回一个1到N的值。一个字符串表是被“,”分隔的子串组成的一个字符串。如果第一个参数是一个常数字符串并且第二个参数是一种类型为SET的列，FIND_IN_SET()函数被优化而使用位运算！如果str不是在strlist里面或如果strlist是空字符串，返回0。如果任何一个参数是NULL，返回NULL。如果第一个参数包含一个“,”，该函数将工作不正常。 

10.FORMAT

FORMAT(X,D[,locale])

将数字X转为 '#,###,###.##'形式的字符串，D为小数位数。

mysql> SELECT FORMAT(12332.123456, 4);
        -> '12,332.1235'
mysql> SELECT FORMAT(12332.1,4);
        -> '12,332.1000'
mysql> SELECT FORMAT(12332.2,0);
        -> '12,332'
mysql> SELECT FORMAT(12332.2,2,'de_DE');
        -> '12.332,20'

　

11.FROM_BASE64/TO_BASE64

FROM_BASE64(str)：解密。

TO_BASE64(str)：加密。　

12.HEX

HEX(str)：返回每个字符的十六进制所组成的字符串。
HEX(N)：返回十六进制值N一个字符串的表示，在此N是一个长整型(BIGINT)数字，这等价于CONV(N,10,16)。如果N是NULL，返回NULL。 

13.INSERT

INSERT(str,pos,len,newstr) 
返回字符串str，在位置pos起始的子串且len个字符长得子串由字符串newstr代替。如果pos非法，返回原串；如果len大于剩余的长度，则剩余的串则newstr代替；如果str是NULL，则返回NULL。 

mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
        -> 'QuWhattic'
mysql> SELECT INSERT('Quadratic', -1, 4, 'What');
        -> 'Quadratic'
mysql> SELECT INSERT('Quadratic', 3, 100, 'What');
        -> 'QuWhat'

　　

14.LOWER/LCASE/UPPER/UCASE

LCASE(str)/LOWER(str) ：返回字符串str，根据当前字符集映射(缺省是ISO-8859-1 Latin1)把所有的字符改变成小写。该函数对多字节是可靠的。

UCASE(str)/UPPER(str) ：返回字符串str，根据当前字符集映射(缺省是ISO-8859-1 Latin1)把所有的字符改变成大写。该函数对多字节是可靠的。

15.LELT/RIGHT

LEFT(str,len) 
返回字符串str的最左面len个字符。如果有参数为NULL，则返回NULL。

RIGHT(str,len) 
返回字符串str的最右面len个字符。 如果有参数为NULL，则返回NULL。

16.LOAD_FILE

LOAD_FILE(file_name) 
读入文件并且作为一个字符串返回文件内容。文件必须在服务器上，你必须指定到文件的完整路径名，而且你必须有file权限。文件必须所有内容都是可读的并且小于max_allowed_packet。如果文件不存在或由于上面原因之一不能被读出，函数返回NULL。

 

17.LOCATE

该函数是多字节可靠的。 

LOCATE(substr,str) 
返回子串substr在字符串str第一个出现的位置，如果substr不是在str里面，返回0. 　

LOCATE(substr,str,pos) 
返回子串substr在字符串str第一个出现的位置，从位置pos开始。如果substr不是在str里面，返回0。

mysql> SELECT LOCATE('bar', 'foobarbar');
        -> 4
mysql> SELECT LOCATE('xbar', 'foobar');
        -> 0
mysql> SELECT LOCATE('bar', 'foobarbar', 5);
        -> 7

　

18.LPAD/RPAD

LPAD(str,len,padstr) 
返回字符串str，左面用字符串padstr填补直到str是len个字符长。 
RPAD(str,len,padstr) 
返回字符串str，右面用字符串padstr填补直到str是len个字符长。

19.TRIM/LTRIM/RTRIM

TRIM([BOTH | LEADING | TRAILING] [remstr] FROM] str) 
返回字符串str，其所有remstr前缀或后缀被删除了。如果没有修饰符BOTH、LEADING或TRAILING给出，BOTH被假定。如果remstr没被指定，空格被删除。

LTRIM(str) 
返回删除了其前置空格字符的字符串str。

RTRIM(str) 
返回删除了其拖后空格字符的字符串str。

mysql> select TRIM(' bar ');
-> 'bar'
mysql> select TRIM(LEADING 'x' FROM 'xxxbarxxx');
-> 'barxxx'
mysql> select TRIM(BOTH 'x' FROM 'xxxbarxxx');
-> 'bar'
mysql> select TRIM(TRAILING 'xyz' FROM 'barxxyz');
-> 'barx'

20.MAKE_SET

MAKE_SET(bits,str1,str2,...) 
返回一个集合 (包含由“,”字符分隔的子串组成的一个字符串)，由相应的位在bits集合中的的字符串组成。str1对应于位0，str2对应位1，等等。在str1, str2, ...中的NULL串不添加到结果中。

mysql> SELECT MAKE_SET(1,'a','b','c');
-> 'a'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
-> 'hello,world'
mysql> SELECT MAKE_SET(0,'a','b','c');
-> ''

　

21.OCT

OCT(N) 
返回八进制值N的一个字符串的表示，在此N是一个长整型数字，这等价于CONV(N,10,8)。如果N是NULL，返回NULL。 

　

22.ORD

ORD(str)

如果字符串str最左面字符是一个多字节字符，通过以格式((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...]返回字符的ASCII代码值来返回多字节字符代码。如果最左面的字符不是一个多字节字符。返回与ASCII()函数返回的相同值。

23.REPEAT

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

24.REPLACE

REPLACE(str,from_str,to_str) 
返回字符串str，其字符串from_str的所有出现由字符串to_str代替。 

mysql> select REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'

　

25.REVERSE

REVERSE(str) 
返回颠倒字符顺序的字符串str。 

26.SPACE

 SPACE(N) 
返回由N个空格字符组成的一个字符串。 

27.SUBSTRING

SUBSTRING(str,pos,len) 

从字符串str返回一个len个字符的子串，从位置pos开始。

SUBSTRING(str,pos) 
从字符串str的起始位置pos返回一个子串。

如果pos为负数，则表示从右往左所在的位置；如果len<1，返回空串。

mysql> SELECT SUBSTRING('Quadratically',5);
        -> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
        -> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
        -> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
        -> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3);
        -> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
        -> 'ki'

　　

28.SUBSTRING_INDEX

 SUBSTRING_INDEX(str,delim,count) 
返回从字符串str的第count个出现的分隔符delim之后的子串。如果count是正数，返回最后的分隔符到左边(从左边数) 的所有字符。如果count是负数，返回最后的分隔符到右边的所有字符(从右边数)。 
该函数对多字节是可靠的。

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

29. INET_ATON/INET_NTOA

ip地址与对应int(4B)值的相互转换。

SELECT INET_NTOA(ip) from `ip_t`;//int-->x.x.x.x
SELECT INET_ATON(ip) from `ip_t`;//x.x.x.x-->int

30.STRCPM()

STRCMP(expr1,expr2)

大当前排序集下比较两个字符串的大小。如果str1=str2，则返回0；如果str1>str2，则返回1；否则，返回-1。

　

参考：

• MySql 字符串函数