mysql string functions

If the length of the result is greater than the maximum value of the max_allowed_packet system variable, the return value of the string value function is NULL.

For functions that operate on string positions, the first position is numbered 1.

ASCII(str)

The return value is the numerical value of the leftmost character of the string str. If str is an empty string, the return value is 0. If str is NULL, the return value is NULL. ASCII() is used for characters with numeric values ​​from 0 to 255.

mysql> SELECT ASCII('2');

-> 50

mysql> SELECT ASCII(2);

-> 50

mysql>

-> ; 100

See ORD() function.

BIN(N)

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

mysql> SELECT BIN(12);

-> '1100'

BIT_LENGTH(str)

The return value is the length of the binary string str.

mysql> SELECT BIT_LENGTH('text');

-> 32

CHAR(N,... [USING charset])

CHAR() understands each parameter N as an integer and its return value A string containing the characters given by the code values ​​of these integers. NULL values ​​are omitted.

mysql> SELECT CHAR(77,121,83,81,'76');

-> 'MySQL'

mysql>

CHAR() parameters greater than 255 are converted to multiple result characters. For example, CHAR(256) is equivalent to CHAR(1,0), and CHAR(256*256) is equivalent to CHAR(1,0,0):

mysql> SELECT HEX(CHAR(1,0)), HEX (CHAR(256));

+----------------+----------------+

| HEX(CHAR (1,0)) | HEX(CHAR(256)) |

+----------------+------------------ -+

| 0100                                                                                                                                                          SELECT HEX(CHAR(1,0,0)), HEX(CHAR(256*256));

+------------------+----- ---------------+

| HEX(CHAR(1,0,0)) | HEX(CHAR(256*256)) |

+------ ------------+------------------------+

| 010000                                                                                        ------------+---------------------+

CHAR()'s return value is a binary string. You can optionally use the USING statement to generate a string in the given character set:

mysql> SELECT CHARSET(CHAR(0x65)), CHARSET(CHAR(0x65 USING utf8));
+---------------------+--------------------------------+
| CHARSET(CHAR(0x65)) | CHARSET(CHAR(0x65 USING utf8)) |
+---------------------+--------------------------------+
| binary              | utf8                           |
+---------------------+--------------------------------+

If USING has been generated and the resulting string does not conform to the given character set, a warning will be issued. Likewise, if strict SQL mode is activated, the result of CHAR() will be NULL.

CHAR_LENGTH(str)

The return value is the length of the string str, and the length unit is characters. A multibyte character counts as a single character. For a set of five two-byte characters, LENGTH() returns 10, while CHAR_LENGTH() returns 5.

CHARACTER_LENGTH(str)

CHARACTER_LENGTH() is a synonym for CHAR_LENGTH().

COMPRESS(string_to_compress)

Compress a string. This function requires that MySQL has been compressed using a compression library such as zlib. Otherwise, the return value is always NULL. UNCOMPRESS() can decompress the compressed string.

mysql> SELECT LENGTH(COMPRESS(REPEAT('a',1000)));

-> 21

mysql> SELECT LENGTH(COMPRESS(''));

-> 0

mysql> ; SELECT LENGTH(COMPRESS('a'));

-> 13

mysql> SELECT LENGTH(COMPRESS(REPEAT('a',16)));

-> 15

Compressed string The contents are stored as follows:

Empty strings are stored as empty strings.

Non-empty string The four-byte length of the uncompressed string is stored (low byte first), followed by the compressed string. If the string ends with a space, a "." will be added at the end to prevent the trailing space from being automatically removed when the result value is stored in a field column of CHAR or VARCHAR type. (It is not recommended to use CHAR or VARCHAR to store compressed strings. It is better to use a BLOB column instead).

CONCAT(str1,str2,...)

The return result is the string generated by the connection parameters. If any parameter is NULL, the return value is NULL. There may be one or more parameters. If all arguments are non-binary strings, the result is a non-binary string. If the argument contains any binary string, the result is a binary string. A numeric argument is converted to its equivalent binary string format; to avoid this, use an explicit type cast, for example: SELECT CONCAT(CAST(int_col AS CHAR), char_col)

mysql> SELECT CONCAT( 'My', 'S', 'QL');

-> 'MySQL'

mysql> SELECT CONCAT('My', NULL, 'QL');

-> NULL

mysql>

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

CONCAT_WS() stands for CONCAT With Separator and is a special form of CONCAT(). The first parameter is the separator for 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. If the delimiter is NULL, the result is NULL. The function ignores NULL values ​​after any delimiter argument.

mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name');

-> 'First name,Second name,Last Name'

mysql> SELECT CONCAT_WS(' ,','First name',NULL,'Last Name');

-> 'First name,Last Name'

CONCAT_WS() will not ignore any empty string. (However, all NULLs are ignored).

CONV(N,from_base,to_base)

Convert numbers between different number bases. The return value is an N string representation of a number, converted from the from_base base to the to_base base. If any parameter is NULL, the return value is NULL. The argument N is understood as an integer, but can be specified as an integer or a string. The minimum base is 2 and the maximum 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() operates 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'

ELT(N,str1,str2,str3,...)

If N = 1, the return value is str1, if N = 2, the return value is str2, and so on. If N is less than 1 or greater than the number of parameters, the return value is NULL. ELT() is the complement of FIELD().

mysql> SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');

-> 'ej'

mysql> SELECT ELT(4, 'ej', 'Heja' , 'hej', 'foo');

-> 'foo'

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

The return value is a string, where for the bits value For each bit group, you can get an on string, and for each cleared bit, you can get an off string. The bit values ​​in bits are checked from right to left (low-order bits to high-order bits). Strings separated by a delimiter string (default comma ',') are added to the result in left-to-right order. number_of_bits will give the number of bits being tested (default is 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'

FIELD(str,str1,str2,str3, ...)

The return value is str1, str2, str3,...the str index in the list. If str is not found, the return value is 0.

If all parameters to FIELD() are strings, all parameters will be compared as strings. If all arguments are numeric, comparisons are made numerically. Otherwise, parameters are compared as double.

If str is NULL, the return value is 0 because NULL cannot be compared equally with any value. FIELD() is the complement of ELT().

mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');

-> 2

mysql> SELECT FIELD('fo', ' Hej', 'ej', 'Heja', 'hej', 'foo');

-> 0

FIND_IN_SET(str,strlist)

If the string str is in a string list composed of N subchains strlist, the return value range is between 1 and N. A string list is a chain of strings 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 calculations. If str is not in strlist or strlist is an empty string, the return value is 0. If any parameter is NULL, the return value is NULL. This function will not work properly if the first argument contains a comma (‘,’).

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

-> 2

FORMAT(X,D)

Set number

HEX(N_or_S)

If N_OR_S is a number, returns a string representation of the hexadecimal value N , where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,16).

If N_OR_S is a string, the return value is a hexadecimal string representation of N_OR_S, where each character in each N_OR_S is converted into two hexadecimal digits.

mysql> SELECT HEX(255);

-> 'FF'

mysql> SELECT 0x616263;

-> 616263

INSERT(str,pos,len,newstr)

Returns the string str, whose substring starts at position pos and the len character replaced by the string newstr. If pos exceeds the string length, the return value is the original string. If the length of len is greater than the length of other strings, replacement starts from position pos. If any parameter is null, the return value is 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'

This function supports multi-byte characters.

INSTR(str,substr)

Returns the first occurrence position of substring in string str. This is the same as the two-argument form of LOCATE(), except that the order of the arguments is reversed.

mysql> SELECT INSTR('foobarbar', 'bar');

-> 4

mysql> SELECT INSTR('xbar', 'foobar');

-> 0

This function supports multiple byte characters, and is case-sensitive only if at least one argument is a binary string.

LCASE(str)

LCASE() is a synonym for LOWER().

LEFT(str,len)

Returns the leftmost character of len starting from the string str.

mysql> SELECT LEFT('foobarbar', 5);

-> 'fooba'

LENGTH(str)

The return value is the length of the string str, in bytes. A multibyte character counts as multibyte. This means that for a string containing five 2-byte characters, LENGTH() returns 10, while CHAR_LENGTH() returns 5.

mysql> SELECT LENGTH('text');

-> 4

LOAD_FILE(file_name)

Read the file and return this file in string format. The location of the file must be on the server, you must give the file a full pathname, and you must have the FILE privilege. The file must be readable and the file size must be less than max_allowed_packet bytes.

If the file does not exist, or cannot be read because it does not meet the above conditions, the function return value is NULL.

mysql> UPDATE tbl_name

SET blob_column=LOAD_FILE('/tmp/picture')

WHERE id=1;

LOCATE(substr,str) , LOCATE(substr,str,pos)

First syntax Returns the first occurrence of substring substr in string str. The second syntax returns the first occurrence position of the substring substr in the string str, starting at pos. If substr is not in str, the return value is 0.

mysql> SELECT LOCATE('bar', 'foobarbar');

-> 4

mysql> SELECT LOCATE('xbar', 'foobar');

-> 0

mysql> LOCATE ('bar', 'foobarbar',5);

-> 7

This function supports multi-byte characters and is case-sensitive only if at least one argument is a binary string.

LOWER(str)

Returns the string str and all characters changed to lowercase letters according to the latest character set mapping table (default is cp1252 Latin1).

mysql> SELECT LOWER('QUADRATICALLY');

-> 'quadratically'

This function supports multi-byte characters.

LPAD(str,len,padstr)

returns the string str, with the left side padded by the string padstr to the length of len characters. If the length of str is greater than len, the return value is shortened to len characters.

mysql> SELECT LPAD('hi',4,'??');

-> '??hi'

mysql> SELECT LPAD('hi',1,'??');

-> 'h'

LTRIM(str)

Returns the string str with leading space characters removed.

mysql> SELECT LTRIM(' barbar');

-> 'barbar'

This function supports multi-byte characters.

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(1 | 4,'hello','nice',NULL,'world');

-> 'hello'

mysql> SELECT MAKE_SET(0,'a','b','c');

-> ''

MID(str,pos,len)

MID(str,pos,len) 是 SUBSTRING(str,pos,len)的同义词。

OCT(N)

返回一个 N的八进制值的字符串表示，其中 N 是一个longlong (BIGINT)数。这等同于CONV(N,10,8)。若N 为 NULL ，则返回值为NULL。

mysql> SELECT OCT(12);

-> '14'

OCTET_LENGTH(str)

OCTET_LENGTH() 是 LENGTH()的同义词。

ORD(str)

若字符串str 的最左字符是一个多字节字符，则返回该字符的代码， 代码的计算通过使用以下公式计算其组成字节的数值而得出:

   (1st byte code)
+ (2nd byte code × 256)
+ (3rd byte code × 2562) ...

假如最左字符不是一个多字节字符，那么 ORD()和函数ASCII()返回相同的值。

mysql> SELECT ORD('2');

-> 50

POSITION(substr IN str)

POSITION(substr IN str)是 LOCATE(substr,str)同义词。

QUOTE(str)

引证一个字符串，由此产生一个在SQL语句中可用作完全转义数据值的结果。  返回的字符串由单引号标注，每例都带有单引号 (‘'’)、 反斜线符号 (‘\’)、 ASCII NUL以及前面有反斜线符号的Control-Z 。如果自变量的值为NULL, 则返回不带单引号的单词 “NULL”。

mysql> SELECT QUOTE('Don\'t!');

-> 'Don\'t!'

mysql> SELECT QUOTE(NULL);

-> NULL

REPEAT(str,count)

返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。 若 count aa9166a2c05d262cc43406462b632926 SELECT REPEAT('MySQL', 3);

-> 'MySQLMySQLMySQL'

REPLACE(str,from_str,to_str)

返回字符串str 以及所有被字符串to_str替代的字符串from_str 。

mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');

-> 'WwWwWw.mysql.com'

这个函数支持多字节字元。

REVERSE(str)

返回字符串 str ，顺序和字符顺序相反。

mysql> SELECT REVERSE('abc');

-> 'cba'

这个函数支持多字节字元。

RIGHT(str,len)

从字符串str 开始，返回最右len 字符。

mysql> SELECT RIGHT('foobarbar', 4);

-> 'rbar'

这个函数支持多字节字元。

RPAD(str,len,padstr)

返回字符串str, 其右边被字符串 padstr填补至len 字符长度。假如字符串str 的长度大于 len,则返回值被缩短到与 len 字符相同长度。

mysql> SELECT RPAD('hi',5,'?');

-> 'hi???'

mysql> SELECT RPAD('hi',1,'?');

-> 'h'

这个函数支持多字节字元。

RTRIM(str)

返回字符串 str ，结尾空格字符被删去。

mysql> SELECT RTRIM('barbar   ');

-> 'barbar'

这个函数支持多字节字元。

SOUNDEX(str)

从str返回一个soundex字符串。 两个具有几乎同样探测的字符串应该具有同样的 soundex 字符串。一个标准的soundex字符串的长度为4个字符，然而SOUNDEX() 函数会返回一个人以长度的字符串。 可使用结果中的SUBSTRING() 来得到一个标准 soundex 字符串。在str中，会忽略所有未按照字母顺序排列的字符。 所有不在A-Z范围之内的国际字母符号被视为元音字母。

mysql> SELECT SOUNDEX('Hello');

-> 'H400'

mysql> SELECT SOUNDEX('Quadratically');

-> 'Q36324'

Note: This function implements the original Soundex algorithm, not the more popular enhanced version (as described by D. Knuth). The difference is that the original version removes vowels first, then repetitions, while the enhanced version removes repetitions first, then vowels.

expr1 SOUNDS LIKE expr2

This is equivalent to SOUNDEX(expr1) = SOUNDEX(expr2).

SPACE(N)

Returns a string consisting of N space symbols.

mysql> SELECT SPACE(6);

-> ' ' '

SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)

The format without the len parameter returns a substring from the string str, starting at position pos. The format with the len parameter returns a substring of the same length as len characters from the string str, starting at position pos. The format using FROM is standard SQL syntax. It is also possible to use a negative value for pos. If so, the substring position starts at the pos character at the end of the string, not at the beginning of the string. You can use a negative value for pos in functions of the following format.

mysql> SELECT SUBSTRING('Quadratically',5);

-> 'ratically'

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'

This function supports multi-byte characters Yuan.

Note that if a value less than 1 is used for len, the result will always be an empty string.

SUBSTR() is a synonym for SUBSTRING().

SUBSTRING_INDEX(str,delim,count)

Returns the self-string from the string str before the delimiters delim and count appear. If count is a positive value, everything to the left of the final delimiter (starting from the left) is returned. If count is negative, everything to the right of the delimiter (starting from the right) is returned.

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

-> 'www.mysql'

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

-> 'mysql.com'

This function supports multi-byte characters.

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)

Returns the string str where all remstr prefixes and/or suffixes have been removed. If none of the classifiers BOTH, LEADIN or TRAILING is given, BOTH is assumed. remstr is optional, and spaces can be removed if not specified.

mysql> SELECT TRIM(' bar ');

-> 'bar'

mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); TRIM(BOTH 'x' FROM 'xxxbarxxx');

-> 'bar'

mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');

-> 'barx'

This function supports Multibyte characters.

UCASE(str)

UCASE() is a synonym for UPPER().

UNCOMPRESS(string_to_uncompress)

Decompress the string compressed by the COMPRESS() function. If the argument is a compressed value, the result is NULL. This function requires that MySQL has been compiled with a compression library such as zlib. Otherwise, the return value will always be NULL.

mysql> SELECT UNCOMPRESS(COMPRESS('any string'));

-> 'any string'

mysql> )

Returns the length of the compressed string before compression.

mysql> SELECT UNCOMPRESSED_LENGTH(COMPRESS(REPEAT('a',30)));

-> 30

UNHEX(str)

Performs the reverse operation from HEX(str). That is, it understands each pair of hexadecimal digits in the parameter as a number and converts it into the character represented by that number. The resulting characters are returned as a binary string.

mysql> SELECT UNHEX('4D7953514C');

-> 'MySQL'

mysql> SELECT UNHEX(HEX('string'));

-> 'string'

mysql> SELECT HEX(UNHEX('1267'));

-> '1267'

UPPER(str)

Returns the string str, and maps according to the latest character set Characters converted to uppercase letters (default is cp1252 Latin1).

mysql> SELECT UPPER('Hej');

-> 'HEJ'

This function supports multi-byte characters.

1. String comparison function

According to MySQL, it will automatically convert numbers into strings and vice versa.

mysql> SELECT 1+'1';

-> 2

mysql> SELECT CONCAT(2,' test');

-> '2 test'

If you want to convert the numbers clearly For strings, you can use the CAST() or CONCAT() function:

mysql> SELECT 38.8, CAST(38.8 AS CHAR);

-> 38.8, '38.8'

mysql> SELECT 38.8, CONCAT(38.8) ;

-> 38.8, '38.8'

CAST() is preferable. .

If a binary string has been given as a parameter to a string function, the resulting string will also be a binary string. A number converted to a string is treated as a binary string. This only affects the comparison results.

Generally speaking, if any expression in a string comparison is case-sensitive, the comparison will also be case-sensitive.

expr LIKE pat [ESCAPE 'escape-char']

Pattern matching, using SQL simple regular expression comparison. Returns 1 (TRUE) or 0 (FALSE). If either expr or pat is NULL, the result is NULL.

Pattern does not need to be a literal string. For example, it can be specified as a string expression or table column.

The following two wildcards can be used with LIKE in the pattern:

Character Description

% Matches any number of characters, even zero characters

_ Can only match one type of character

mysql> SELECT 'David!' LIKE 'David_';

-> 1

mysql> SELECT 'David!' LIKE '%D%v%';

-> 1

To test literal instances of wildcards, you can convert The meaning character is placed in front of this character. If the ESCAPE character is not specified, ' ' is assumed.

String Description

% Matches a '%' character

_ Matches a '_' character

mysql> SELECT 'David!' LIKE 'David_';

-> 0

mysql& gt; SELECT 'David_ ' LIKE 'David_';

-> 1

To specify a different escape character, you can use the ESCAPE statement:

mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';

- > 1

The escape sequence can be empty or one character in length. Starting from MySQL 5.1.2, if the NO_BACKSLASH_ESCAPES SQL mode is activated, the sequence cannot be empty.

The following two statements illustrate that string comparisons are case-insensitive unless one of the operands is a binary string:

mysql> SELECT 'abc' LIKE 'ABC';

-> 1

mysql> SELECT 'abc' LIKE BINARY 'ABC';

-> 0

In MySQL, LIKE is allowed to appear in numeric expressions. (This is an extension of standard SQL LIKE).

mysql> SELECT 10 LIKE '1%';

-> 1

Note: Since MySQL uses C escape syntax in strings (for example, use 'n' to represent a newline character), in the LIKE string , the '' used must be double-written. For example, to find ‘n’, you must write it as ‘\n’. If you want to find '', you must write it as '\\'; the reason is that the backslash symbol will be stripped once by the syntax parser, and will be stripped again when performing pattern matching, and finally there will be one Backslash symbols are accepted for matching.

expr NOT LIKE pat [ESCAPE 'escape-char']

This is equivalent to NOT (expr LIKE pat [ESCAPE 'escape-char']).

expr NOT REGEXP pat expr NOT RLIKE pat

This is equivalent to NOT (expr REGEXP pat).

expr REGEXP pat expr RLIKE pat

Performs pattern matching of string expression expr and pattern pat. The pattern can be extended to a regular expression. If expr matches pat, return 1; otherwise return 0. If either expr or pat is NULL, the result is NULL. RLIKE is a synonym for REGEXP, and its function is to provide compatibility for mSQL.

Pattern does not need to be a literal string. For example, it can be specified as a string expression or table column.

Note: Since MySQL uses C escape syntax in strings (for example, using ‘n’ to represent newline characters), the ‘’ used in the REGEXP string must be doubled.

REGEXP is not case sensitive unless it is used with a binary string.

mysql> SELECT 'Monty!' REGEXP 'm%y%%';

-> 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

When determining the character type, REGEXP and RLIKE use the current character set (Default is cp1252 Latin1). Warning: These operators do not support multibyte characters.

STRCMP(expr1,expr2)

If all strings are the same, STRCMP() is returned. If the first parameter is less than the second parameter according to the current classification order, -1 is returned, and 1 is returned in other cases.

mysql> SELECT STRCMP('text', 'text2');

-> -1

mysql> STRCMP('text', 'text');

-> 0

STRCMP() uses the current character set when performing comparisons. This makes the default comparison case-sensitive except when one or both operands are binary strings.