Detailed explanation of MySQL string functions (recommended)

1. ASCII

ASCII(str)

Returns the ASCII code value of the leftmost character of the string str. If str is the empty string, 0 is returned. If str is NULL, return NULL.

2. ORD

ORD(str)

If the leftmost character of the string str is a multi-byte character, use the format ((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...] returns the ASCII code value of the character to return the multibyte character code. If the leftmost character is not a multibyte character. Returns the same value returned by the ASCII() function.

3. CONV

CONV(N,from_base,to_base)
Convert numbers between different number bases. Returns the string number of the number N, transformed from the base from_base to the base to_base, or NULL if any argument is NULL. The N parameter is interpreted as an integer, but can be specified as an integer or a string. The smallest basis is 2 and the largest basis is 36. If to_base is a negative number, N is treated as a signed number, otherwise, N is treated as an unsigned number. CONV works with 64-bit precision.

mysql> select CONV("a",16,2);

-> '1010'

mysql> select CONV("6E",18,8);
  
-> '172'
  
mysql> select CONV(-17,10,-18);
  
-> '-H'
  
mysql> select CONV(10+"10"+'10'+0xa,10,10);
  
-> '40'

4. BIN

BIN(n)

Returns a string representation of the binary value N, where N is a long integer (BIGINT) number, which is equivalent to CONV(N,10,2 ). If N is NULL, return NULL.

5. OCT

OCT(N)
Returns a string representation of the octal value N, where N is a long integer number, which is equivalent to CONV(N,10,8). If N is NULL, return NULL.

6. HEX

HEX(N)
Returns a string representation of the hexadecimal value N, where N is a long integer (BIGINT) number, which is equivalent to CONV(N,10,16). If N is NULL, return NULL.
mysql> select HEX(255);

7. CHAR

CHAR(N,...)
CHAR() interprets the parameters as integers and returns a string composed of the ASCII code characters of these integers. NULL values ​​are skipped.

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

8. CONCAT/CONCAT_WS

•CONCAT(str1,str2,...)

Returns the string from the parameter connection. If any argument is NULL, NULL is returned. Can have more than 2 parameters. A numeric argument is converted to its equivalent string form.

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() represents CONCAT With Separator , is a special form of CONCAT(). The first parameter is the delimiter for the other parameters. The position of the delimiter is placed between the two strings to be concatenated. The delimiter can be a string or other parameters.

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

Nine, LENGTH/OCTET_LENGTH/CHAR_LENGTH/CHARACTER_LENGTH

LENGTH(str)/OCTET_LENGTH(str): number of bytes

CHAR_LENGTH(str)/CHARACTER_LENGTH(str): number of characters

十、LOCATE

This function is multi-byte reliable.

LOCATE(substr,str)
Returns the position where the substring substr first appears in the string str. If substr is not in str, returns 0.

LOCATE(substr,str,pos)
Returns the position of the first occurrence of substring substr in string str, starting from position pos. If substr is not in str, return 0.

11. LPAD/RPAD

LPAD(str,len,padstr)
Returns the string str, fill it with the string padstr on the left until str is len characters long.
RPAD(str,len,padstr)
Returns the string str, padded on the right with the string padstr until str is len characters long.

Twelve, LELT/RIGHT

LEFT(str,len)
Returns the leftmost len ​​characters of the string str.

RIGHT(str,len)
Returns the rightmost len ​​characters of the string str.

13. SUBSTRING

SUBSTRING (str, pos, len)

Returns a substring of len characters from the string str, starting from position pos.

SUBSTRING(str,pos)

Returns a substring from the starting position pos of the string str.

Fourteen, SUBSTRING_INDEX

SUBSTRING_INDEX(str,delim,count)

Returns the substring after the count-th occurrence of the delimiter delim from the string str. If count is positive, return the last delimiter to the left (counting from the left) of all characters. If count is negative, returns all characters to the right of the last delimiter (counting from the right).
This function is reliable for multibytes.

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

Fifteen, TRIM/LTRIM/RTRIM

TRIM([BOTH | LEADING | TRAILING] [remstr] FROM] str)
Returns the string str with all remstr prefixes or suffixes removed. If no modifiers BOTH, LEADING or TRAILING are given, BOTH is assumed. If remstr is not specified, spaces are removed.

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'

LTRIM(str)
Returns the string str with its leading space characters removed.

RTRIM(str)
Returns the string str with its trailing space characters removed.

Sixteen, SPACE

SPACE(N)
Returns a string composed of N space characters.

Seventeen, REPLACE

REPLACE(str,from_str,to_str)
Returns the string str, in which all occurrences of the string from_str are replaced by the string to_str.

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

18. REPEAT

REPEAT(str,count)
Returns a string consisting of the string str repeated countTimes times. if count

Nineteen, REVERSE

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

二十、INSERT

INSERT(str,pos,len,newstr)
返回字符串str，在位置pos起始的子串且len个字符长得子串由字符串newstr代替。  

mysql> select INSERT('Quadratic', 3, 4, 'What');
  
-> 'QuWhattic'

   

二十一、ELT

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

二十二、FIELD

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

二十三、FIND_IN_SET

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

二十四、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');
  
-> ''

   

二十五、EXPORT_SET

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

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

二十六、LOWER/LCASE/UPPER/UCASE

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

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

二十七、LOAD_FILE

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

以上就是小编为大家带来的MySQL字符串函数详解(推荐)全部内容了，更多相关文章请关注PHP中文网（www.php.cn）！