MySQL string functions

String is a finite sequence composed of zero or multiple characters. It is generally recorded as s = 'a1a2 • an '(n> = 0 ). It is the data type that represents text in programming languages.

Generally, the entire string is used as the operation object, for example: search for a substring in a substring, obtain a substring, insert a substring at a certain position in the substring, and delete a substring. The required and sufficient conditions for the two strings to be equal are: the length is equal, and the characters at each corresponding position are equal. If p and q are two strings, the calculation of the position where q first appeared in p is called pattern matching. The two most basic storage methods of strings are sequential storage and link storage.

Let's take a look at the string functions in MySQL.

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

For a function operated at the string position, the first position is numbered 1.

◆ ASCII (str)

The return value is the leftmost character of the str string. If str is a null string, the return value is 0. If 'str' is NULL, the return value is NULL. ASCII () is a character with a value ranging from 0 to 255.

Mysql> select ascii ('2 ');

-> 50

Mysql> select ascii (2 );

-> 50

Mysql> select ascii ('dx ');

-> 100


See the ORD () function.

◆ BIN (N)

The string that returns 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);-> '20140901'

◆ BIT_LENGTH (str)

Returns the str length of a binary string.

Mysql> SELECT BIT_LENGTH ('text');-> 32

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

CHAR () interprets each parameter N as an integer, and its return value is a string containing the characters given by the code values of these integers. The NULL value is omitted.

Mysql> select char (77,121, 77.3, '76');-> 'mysql' MySQL> select char (77, '77. 3');-> 'mmm'

The CHAR () parameter greater than 255 is converted to multi-result characters. For example, CHAR (256) is equivalent to CHAR (256), while CHAR (256 *) is equivalent to CHAR (, 0 ):

Mysql> select hex (CHAR (256), HEX (CHAR (256); + ---------------- + | HEX (CHAR )) | + ---------------- + | 0100 | 0100 | + ------------------ + -------------- + mysql> select hex (CHAR (256, 0, 0), HEX (CHAR (256 )); + ------------------ + | HEX (CHAR (256, 0) | HEX (CHAR (256 )) | + ------------------ + | 010000 | 010000 | + -------------------- + ---------------------- +

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

Mysql> select charset (CHAR (0x65), CHARSET (CHAR (0x65 USING utf8); 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 result string does not conform to the given character set, a warning is issued. Similarly, if the strict SQL mode is activated, the CHAR () result will become NULL.

◆ CHAR_LENGTH (str)

The return value is the length of the str string, measured in characters. A multi-byte character is counted as a single character. For a five-Byte Character Set, 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 extract compressed strings.

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

The content of the compressed string is stored as follows:

Empty strings are stored as empty strings.

The four-byte length of a non-empty string that is not compressed is stored (the first is low byte), followed by a compressed string. If the string ends with a space, ". ", to prevent the result value from automatically removing trailing spaces when it is stored in a CHAR or VARCHAR field column. (CHAR or VARCHAR is not recommended for storing compressed strings. It is best to use a BLOB column instead ).

◆ CONCAT (str1, str2 ,...)

Returns the string generated by the connection parameter. If any parameter is NULL, the return value is NULL. There may be one or more parameters. If all parameters are non-binary strings, the result is a non-binary string. If the independent variable contains any binary string, the result is a binary string. A numeric parameter is converted to an equivalent binary string format. To avoid this, you can use an explicit 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 '); -> NULLmysql> select concat (14.3);-> '14. 3'

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

CONCAT_WS () represents CONCAT With Separator, which is a special form of CONCAT. The first parameter is the delimiter of other parameters. The separator is placed between the two strings to be connected. The delimiter can be a string or another parameter. If the Delimiter is NULL, the result is NULL. The function ignores the NULL value after any separator parameter.

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 () does not ignore any null strings. (However, all NULL values are ignored ).

◆ CONV (N, from_base, to_base)

Convert numbers between different bases. The return value is the N string of the number, which is converted from from_base to to_base base. If any parameter is NULL, the return value is NULL. The Independent Variable N is interpreted as an integer, but can be specified as an integer or string. The minimum base is 2, and the maximum base is 36. If to_base is a negative number, N is considered as a signed number. Otherwise, N is considered as the unsigned number. The CONV () operation precision is 64 bits.

Mysql> select conv ('A', 1010);-> '000000' mysql> select conv ('6e ); -> '000000' mysql> select conv (-172,-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. For each bit group in the bits value, you can obtain an on string. For each bit that is cleared, you can obtain an off string. Bits are tested in the order from right to left (from low bit to high bit ). Strings are separated by commas (,) and added to the results in the left-to-right order. Number_of_bits indicates the tested binary digits (64 by default ).

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

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

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

If all parameters for FIELD () are strings, all parameters are compared by string. If all parameters are numbers, compare them by numbers. Otherwise, the parameters are compared in double mode.

If str is NULL, the return value is 0 because NULL cannot be compared 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 str string is in the strlist consisting of N substrings, the return value ranges from 1 to N. A string list is a string consisting of several tokens separated by commas. If the first parameter is a constant string and the second parameter is the type SET column, the FIND_IN_SET () function is optimized and computed in bits. If str is not in strlist or strlist is a null string, the return value is 0. If any parameter is NULL, the return value is NULL. This function cannot run normally when the first parameter contains a comma.

Mysql> SELECT FIND_IN_SET ('B', 'a, B, c, D');-> 2

◆ FORMAT (X, D)

Set number X to the format '#,###,########'. The number X is rounded to the D-digit after the decimal point, and the returned result is a string.

◆ HEX (N_or_S)

If N_OR_S is a number, a string with a hexadecimal value of N is returned. Here, 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 of N_OR_S. Each character in N_OR_S is converted to two hexadecimal numbers.

Mysql> select hex (255);-> 'ff 'mysql> SELECT 0x616263;-> 'abc' mysql> select hex ('abc');-> 616263





◆ INSERT (str, pos, len, newstr)

Returns the str string. Its substring starts from the pos position and is long replaced by the string newstr. If the pos length exceeds the string length, the return value is the original string. If len is longer than other strings, it is replaced by pos. If any parameter is null, the return value is NULL.

Mysql> select insert ('quadratic ', 3, 4, 'who');-> 'quwhattic' mysql> select insert ('quadratic',-1, 4, 'who');-> 'quadratic 'mysql> select insert ('quadratic ', 3,100, 'who');-> 'quwh'

This function supports multi-byte characters.

◆ INSTR (str, substr)

Returns the first occurrence position of the str substring. This is the same as the double parameter form of LOCATE () unless the order of parameters is reversed.

Mysql> select instr ('foobar', 'bar');-> 4 mysql> select instr ('xbar', 'foobar');-> 0

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

◆ LCASE (str)

LCASE () is a synonym for LOWER.

◆ LEFT (str, len)

Returns the leftmost len character starting with str.

Mysql> select left ('foobarbar', 5);-> 'fooba'

◆ LENGTH (str)

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

Mysql> select length ('text');-> 4

◆ LOAD_FILE (file_name)

Read the file and return it in string format. The FILE location must be on the server. You must specify the full path name for the FILE, and you must own the FILE license. The file must be readable, and the file capacity must be smaller than max_allowed_packet bytes.

If the file does not exist or cannot be read because the preceding conditions are not met, the function returns NULL.

Mysql> UPDATE tbl_nameSET blob_column = LOAD_FILE ('/tmp/picture') WHERE id = 1;

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

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

Mysql> select locate ('bar', 'foobar');-> 4 mysql> select locate ('xbar', 'foobar '); -> 0 mysql> select locate ('bar', 'foobarbar', 5);-> 7

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

◆ LOWER (str)

Returns the str string and all characters that are converted to lowercase letters based on the latest character set ing table (default: cp1252 Latin1 ).

Mysql> select lower ('quadratically ');-> 'quadratically'

This function supports multi-byte characters.

◆ LPAD (str, len, padstr)

Returns the str string. The left side of the string is filled by the string padstr To The len Length. If str is longer 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 str, whose leading space character is deleted.

Mysql> select ltrim ('barbar');-> 'barbar'

This function supports multi-byte characters.

◆ MAKE_SET (bits, str1, str2 ,...)

Returns a set value (a string that contains character strings separated by commas (,), consisting of strings with corresponding bits in the bits group. Str1 corresponds to bit 0, str2 corresponds to bit 1, and so on. The NULL Value in str1, str2,... is not added to the result.

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) is a synonym for SUBSTRING (str, pos, len.

◆ OCT (N)

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

Mysql> select oct (12);-> '14'

◆ OCTET_LENGTH (str)

OCTET_LENGTH () is a synonym for LENGTH.

◆ ORD (str)

If the leftmost character of the string 'str' is a multi-byte character, the code of this character is returned. The code is calculated using the following formula to calculate the value of its composition byte:

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

If the leftmost character is not a multi-byte character, the ORD () and ASCII () functions return the same value.

Mysql> select ord ('2');-> 50

◆ POSITION (substr IN str)

POSITION (substr IN str) is a synonym for LOCATE (substr, str.

◆ QUOTE (str)

Quote a string to generate a result that can be used as a fully escaped data value in an SQL statement. The returned string is enclosed in single quotes. Each example contains single quotes ('''), backslash (''), ascii nul, and Control-Z with the backslash symbol. If the value of the independent variable is NULL, the return value is NULL without single quotation marks ".

Mysql> select quote ('don' t! ');-> 'Don' t! 'Mysql> select quote (NULL);-> NULL

◆ REPEAT (str, count)

Returns a string consisting of repeated str strings. The number of str strings is equal to count. If count <= 0, an empty string is returned. If 'str' or 'Count' is NULL, return NULL.

Mysql> select repeat ('mysql', 3);-> 'mysqlmysqlmysqlsql'

◆ REPLACE (str, from_str, to_str)

Returns the str string and the from_str string replaced by the to_str string.

Mysql> select replace ('www .mysql.com ', 'w', 'ww');-> 'wwwwww .mysql.com'

This function supports multi-byte characters.

◆ REVERSE (str)

Returns the str string, which is in the opposite order of characters.

Mysql> select reverse ('abc');-> 'CBA'

This function supports multi-byte characters.

◆ RIGHT (str, len)

Returns the rightmost len character starting from str.

Mysql> select right ('foobarbar', 4);-> 'rbar'

This function supports multi-byte characters.

◆ RPAD (str, len, padstr)

Returns the string 'str', whose right side is filled by the string 'padstr' to the length of 'len. If str is longer than len, the return value is shortened to the same length as len.

Mysql> select rpad ('hi', 5 ,'? ');-> 'Hi ??? 'Mysql> select rpad ('hi', 1 ,'? ');-> 'H'

This function supports multi-byte characters.

◆ RTRIM (str)

Returns the str string, with the trailing space characters deleted.

Mysql> select rtrim ('barbar');-> 'barbar'

This function supports multi-byte characters.

◆ SOUNDEX (str)

Returns a soundex string from str. Two strings with almost identical probes should have the same soundex string. The length of a standard soundex string is 4 characters. However, the SOUNDEX () function returns a string of length. You can use SUBSTRING () in the result to obtain a standard soundex string. In str, all characters not listed alphabetically are ignored. All international alphanumeric symbols not within the scope of the A-Z are considered as vowels.

Mysql> select soundex ('hello');-> 'h400' mysql> select soundex ('quadratically ');-> 'q36324'

Note: This function executes the original Soundex algorithm, rather than the more popular enhanced version (as described in D. Knuth ). The difference is that the original version will delete the vowels first, followed by duplicates, while the enhanced version will delete duplicates first, and then the vowels.

◆ Expr1 sounds like expr2

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

◆ SPACE (N)

Returns a string consisting of N separators.

Mysql> select space (6);->''

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

Returns a substring from the str string in a format that does not contain the len parameter, starting from the position pos. The format with the len parameter returns a substring with the same length as the len character from the string str, starting from the position pos. Use the FROM format as the standard SQL syntax. It may also use a negative value for pos. In this case, the position of the substring starts with the pos character at the end of the string, rather than the start position of the string. You can use a negative value for pos in the following format.

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'

This function supports multi-byte characters.

NOTE: If len is a value less than 1, the result is always a null string.

SUBSTR () is a synonym for SUBSTRING.

◆ SUBSTRING_INDEX (str, delim, count)

Returns the string from str before delim and count. If count is a positive value, all content on the left of the final delimiter (starting from the left) is returned. If count is a negative value, all content on the right of the delimiters (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 str string. All the remstr prefixes and/or suffixes have been deleted. If none of the class characters BOTH, LEADIN, or TRAILING are specified, it is assumed to be BOTH. Remstr is optional. spaces can be deleted if not specified.

Mysql> select trim ('bar');-> 'bar' mysql> select trim (LEADING 'X' FROM 'xxxbarxxx '); -> 'barxx' mysql> select trim (BOTH 'X' FROM 'xxxbarxxx');-> 'bar' mysql> select trim (TRAILING 'xyz' FROM 'barxxyz '); -> 'barx'

This function supports multi-byte characters.

◆ UCASE (str)

UCASE () is a synonym for UPPER.

◆ UNCOMPRESS (string_to_uncompress)

Decompress the string compressed by the COMPRESS () function. If the parameter is a compressed value, the result is NULL. This function requires that MySQL has been compiled by a compressed Library such as zlib. Otherwise, the return value is always NULL.

Mysql> select uncompress (COMPRESS ('any string');-> 'any string' mysql> select uncompress ('any string');-> NULL

◆ UNCOMPRESSED_LENGTH (compressed_string)

Returns the length before compression of the compressed string.

Mysql> SELECT UNCOMPRESSED_LENGTH (COMPRESS (REPEAT ('A', 30);-> 30

◆ UNHEX (str)

Execute the reverse operation from HEX (str. That is to say, it interprets every hexadecimal number in the parameter as a number and converts it to a character represented by this number. The result character is returned as a binary string.

Mysql> select unhex ('4d7953514c');-> 'mysql' MySQL> SELECT 0x4D7953514C;-> 'mysql' mysql> select unhex (HEX ('string ')); -> 'string' mysql> select hex (UNHEX ('000000');-> '20140901'

◆ UPPER (str)

Returns the str string and the characters mapped to uppercase letters based on the latest character set (default: cp1252 Latin1 ).

Mysql> select upper ('hej');-> 'hej'

This function supports multi-byte characters.