MySQL string functions

A String or String is a finite sequence composed of zero or multiple characters. It is generally recorded as sa1a2an (n0 ). 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. Two strings

A String or String is a finite sequence composed of zero or multiple characters. It is generally recorded as s = 'a1a2a' (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. Two strings

A String or 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 two strings are equal. A sufficient condition is that 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 chained 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 );
-> '123'

◆ 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, 81, '76 ');
-> 'Mysql'
Mysql> select char (77, 77.3, '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 ));
+ ---------------- +
| HEX (CHAR (256) | HEX (CHAR () |
+ ---------------- +
| 0100/0100 |
+ ---------------- +
Mysql> select hex (CHAR (256, 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 ');
-> NULL
Mysql> 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 other parameters. 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', 16, 2 );
-> '123'
Mysql> select conv ('6e', 18, 8 );
-> '123'
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. 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'

◆ 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 & gt; 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, 'wh ');
-> 'Quwhattic'
Mysql> select insert ('quadratic ',-1, 4, 'wh ');
-> 'Quadratic'
Mysql> select insert ('quadratic ', 3,100, 'wh ');
-> '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_name
SET 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', 'foobarbar ');
-> 4
Mysql> select locate ('xbar', 'foobar ');
-> 0
Mysql> select locate ('bar', 'foobarbarbar ', 5 );
-> 7

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