MySQL string functions _ MySQL

MySQL string function Daquan bitsCN.com MySQL string functions
For operations on the string position, the first position is marked as 1.
ASCII (str)
Returns the ASCII code value of the leftmost character of the str string. If str is a null string, 0 is returned. If 'str' is NULL, return NULL.
Mysql> select ASCII ('2 ');
-> 50
Mysql> select ASCII (2 );
-> 50
Mysql> select ASCII ('dx ');
-> 100
For more information, see the ORD () function.
ORD (str)
If the leftmost character of a 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 a character to return the multi-byte code. If the leftmost character is not a multi-byte character. Returns the same value as that returned by the ASCII () function.
Mysql> select ORD ('2 ');
-> 50
CONV (N, from_base, to_base)
Convert numbers between different digit bases. Returns the string number of number N, which is converted from from_base base to to_base base. if any parameter is NULL, NULL is returned. Parameter N is interpreted as an integer, but can be specified as an integer or a 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 an unsigned number. CONV works with 64-bit precision.
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'
BIN (N)
Returns a string of the binary value N. Here N is a long integer (BIGINT) number, which is equivalent to CONV (N, 10, 2 ). If N is NULL, return NULL.
Mysql> select BIN (12 );
-> '123'
OCT (N)
Returns the representation of a string with an octal value N. Here N is a long integer, which is equivalent to CONV (N, 10, 8 ). If N is NULL, return NULL.
Mysql> select OCT (12 );
-> '14'
HEX (N)
Returns the representation of a hexadecimal value N string. Here N is a long integer (BIGINT) number, which is equivalent to CONV (N, 10, 16 ). If N is NULL, return NULL.
Mysql & gt; select HEX (255 );
-> 'Ff'
CHAR (N ,...)
CHAR () interprets the parameter as an integer and returns a string consisting of ASCII code characters of these integers. The NULL value is skipped.
Mysql> select CHAR (77,121, 81, '76 ');
-> 'Mysql'
Mysql> select CHAR (77, 77.3, '77. 3 ');
-> 'Mmm'
CONCAT (str1, str2 ,...)
Returns a string from the parameter link. If any parameter is NULL, return NULL. There can be more than two parameters. A numeric parameter is converted to an equivalent string.
Mysql> select CONCAT ('My, s', 'ql ');
-> 'Mysql'
Mysql> select CONCAT ('My, NULL, 'ql ');
-> NULL
Mysql> select CONCAT (14.3 );
-> '14. 3'
LENGTH (str)
　
OCTET_LENGTH (str)
　
CHAR_LENGTH (str)
　
CHARACTER_LENGTH (str)
Returns the length of the str string.
Mysql> select LENGTH ('text ');
-> 4
Mysql> select OCTET_LENGTH ('text ');
-> 4
Note that for multi-byte characters, its CHAR_LENGTH () is calculated only once.
LOCATE (substr, str)
　
POSITION (substr IN str)
Returns the position of the substring substr in the first occurrence of the str. if the substring is not in the str, the return value is 0.
Mysql> select LOCATE ('bar', 'foobarbar ');
-> 4
Mysql> select LOCATE ('xbar', 'foobar ');
-> 0
This function is multi-byte reliable.
LOCATE (substr, str, pos)
Returns the position of the substring substr at the first occurrence of the substring, starting from the position pos. If substr is not in str, 0 is returned.
Mysql> select LOCATE ('bar', 'foobarbarbar ', 5 );
-> 7
This function is multi-byte reliable.
INSTR (str, substr)
Returns the position where the substring substr first appears in the str string. This is the same as LOCATE () in the form of two parameters, except that the parameters are reversed.
Mysql> select INSTR ('foobar', 'bar ');
-> 4
Mysql> select INSTR ('xbar', 'foobar ');
-> 0
This function is multi-byte reliable.
LPAD (str, len, padstr)
Returns the str string. the left side is filled with the string padstr until str is a string of len characters.
Mysql> select LPAD ('hi', 4 ,'?? ');
-> '?? Hi'
RPAD (str, len, padstr)
Returns the str string. fill it with the string padstr on the right until it is a string of len characters.
Mysql> select RPAD ('hi', 5 ,'? ');
-> 'Hi ??? '
LEFT (str, len)
Returns the leftmost len character of the str string.
Mysql> select LEFT ('foobarbar', 5 );
-> 'Fooba'
This function is multi-byte reliable.
RIGHT (str, len)
Returns the rightmost len character of the str string.
Mysql> select RIGHT ('foobarbar', 4 );
-> 'Rbar'
This function is multi-byte reliable.
SUBSTRING (str, pos, len)
　
SUBSTRING (str FROM pos FOR len)
　
MID (str, pos, len)
Returns a substring of len characters from the str string, starting from the position pos. The variant form of FROM is ANSI SQL92 syntax.
Mysql> select SUBSTRING ('quadratically ', 5, 6 );
-> 'Ratica'
This function is multi-byte reliable.
SUBSTRING (str, pos)
　
SUBSTRING (str FROM pos)
Returns a substring from the start position of the str string pos.
Mysql> select SUBSTRING ('quadratically ', 5 );
-> 'Ratically'
Mysql> select SUBSTRING ('foobarbar' FROM 4 );
-> 'Barbar'
This function is multi-byte reliable.
SUBSTRING_INDEX (str, delim, count)
Returns the substring after the delimiter delim that appears from the count of the str string. If count is a positive number, all characters from the last separator to the left (from the left) are returned. If count is a negative number, return all characters (from the right) from the last separator to the right ).
Mysql> select SUBSTRING_INDEX ('www .mysql.com ','. ', 2 );
-> 'Www. mysql'
Mysql> select SUBSTRING_INDEX ('www .mysql.com ','. ',-2 );
-> 'MySQL. com'
This function is reliable for multiple bytes.
LTRIM (str)
Returns the str string with leading space characters deleted.
Mysql> select LTRIM ('barbar ');
-> 'Barbar'
RTRIM (str)
Returns the str string that deletes the trailing space characters.
Mysql> select RTRIM ('barbar ');
-> 'Barbar'
This function is reliable for multiple bytes.
TRIM ([[BOTH | LEA
DING | TRAILING] [remstr] FROM] str)
Returns the str string. all the remstr prefixes or suffixes are deleted. If no modifier BOTH, LEADING, or TRAILING is given, BOTH is assumed. If remstr is not specified, spaces are deleted.
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'
This function is reliable for multiple bytes.
SOUNDEX (str)
Returns a homophone string of str. The two strings that sound "roughly the same" should have the same homophone string. The length of a "standard" homophone string is 4 characters, but the SOUNDEX () function returns a string of any length. You can use SUBSTRING () in the result to obtain a "standard" homophone string. All non-alphanumeric characters are ignored in a given string. All international letters except the A-Z are treated as vowels.
Mysql> select SOUNDEX ('Hello ');
-> 'H400'
Mysql> select SOUNDEX ('quadratically ');
-> 'Q36324'
SPACE (N)
Returns a string consisting of N spaces.
Mysql> select SPACE (6 );
->''
REPLACE (str, from_str, to_str)
Returns the str string, which is replaced by the to_str character string.
Mysql> select REPLACE ('www .mysql.com ', 'W', 'WW ');
-> 'Wwwwww .mysql.com'
This function is reliable for multiple bytes.
REPEAT (str, count)
Returns a string consisting of str strings that repeat countTimes. If count <= 0, an empty string is returned. If 'str' or 'count' is NULL, return NULL.
Mysql> select REPEAT ('mysql', 3 );
-> 'Mysqlmysqlmysqlmysql'
REVERSE (str)
Returns the str string in the reversed character order.
Mysql> select REVERSE ('ABC ');
-> 'CBA'
This function is reliable for multiple bytes.
INSERT (str, pos, len, newstr)
Returns the str string, which is the substring starting from the position pos and is replaced by the newstr string with the len character length.
Mysql> select INSERT ('quadratic ', 3, 4, 'wh ');
-> 'Quwhattic'
This function is reliable for multiple bytes.
ELT (N, str1, str2, str3 ,...)
If N = 1, return str1. if N = 2, return str2, and so on. If N is less than 1 or greater than the number of parameters, NULL is returned. ELT () is a FIELD () inverse operation.
Mysql> select ELT (1, 'EJ', 'heja ', 'hej', 'Foo ');
-> 'EJ'
Mysql> select ELT (4, 'EJ', 'heja ', 'hej', 'Foo ');
-> 'Foo'
FIELD (str, str1, str2, str3 ,...)
Returns the index of str in the str1, str2, str3,... list. If str is not found, 0 is returned. FIELD () is an inverse operation 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 the strlist consisting of N substrings, a value ranging from 1 to N is returned. A string table is a string consisting of substrings separated by commas. If the first parameter is a constant string and the second parameter is a SET column, the FIND_IN_SET () function is optimized and bit operations are used! If str is not in strlist or if strlist is a null string, 0 is returned. If any parameter is NULL, NULL is returned. If the first parameter contains a ",", the function will not work properly.
Mysql> SELECT FIND_IN_SET ('B', 'a, B, c, D ');
-> 2
MAKE_SET (bits, str1, str2 ,...)
Returns a collection (a string consisting of substrings separated by ","), which is composed of the strings of the corresponding bits. Str1 corresponds to bit 0, str2 corresponds to bit 1, and so on. The NULL string 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 (0, 'A', 'B', 'C ');
->''
EXPORT_SET (bits, on, off, [separator, [number_of_bits])
Returns a string. here, for each bit set in "bits", you get a "on" string, and for each reset (reset) bit, you get a "off" string. Each string is separated by "separator" (default ","), and only the "number_of_bits" (default 64) bits are used.
Mysql> select EXPORT_SET (5, 'y', 'n', ',', 4)
-> Y, N, Y, N
LCASE (str)
　
LOWER (str)
Returns str, which converts all characters to lowercase based on the current character set ing (ISO-8859-1 Latin1 by default. This function is reliable for multiple bytes.
Mysql> select LCASE ('quadratically ');
-> 'Quadratically'
UCASE (str)
　
UPPER (str)
Returns str, which converts all characters to uppercase based on the current character set ing (ISO-8859-1 Latin1 by default. This function is reliable for multiple bytes.
Mysql> select UCASE ('hej ');
-> 'Hej'
This function is reliable for multiple bytes.
LOAD_FILE (file_name)
Read the file and return the file content as a string. The file must be on the server, you must specify the full path name of the file, and you must have the file permission. All content of the file must be readable and smaller than max_allowed_packet. If the file does not exist or cannot be read due to one of the above reasons, the function returns NULL.
Mysql> UPDATE table_name
SET blob_column = LOAD_FILE ("/tmp/picture ")
WHERE id = 1;
MySQL automatically converts numbers to strings when necessary, and the reverse is also true:
Mysql> SELECT 1 + "1 ";
-> 2
Mysql> select concat (2, 'test ');
-> '2 test'
If you want to explicitly convert a number to a string, pass it as a parameter to CONCAT ().
If a string function provides a binary string as a parameter, the result string is also a binary string. The number converted to a string is treated as a binary string. This only affects comparison .bitsCN.com