Mysql string processing functions

Mysql string processing functions
ASCII (char) returns the ASCII value of a character
BIT_LENGTH (str) returns the bit length of the string.
CONCAT (s1, s2 ..., Sn) replace s1, s2 ..., Connect the sn to a string
CONCAT_WS (sep, s1, s2 ..., Sn) replace s1, s2 ..., Connect the sn to a string and separate it with the sep character
INSERT (str, x, y, instr): Start from position x of String str. replace substring with string instr with length y. The result is returned.
FIND_IN_SET (str, list) analyzes the list separated by commas (,). if str is found, the position of str in the list is returned.
LCASE (str) or LOWER (str) returns the result of changing all characters in the str string to lowercase.
LEFT (str, x) returns the leftmost x characters in the str string.
LENGTH (s) returns the number of characters in the str string.
LTRIM (str) removes the leading space from the str string.
POSITION (substr, str) returns the POSITION of the substring substr that appears for the first time in the str string.
QUOTE (str) escape single quotes in str with a backslash

REPEAT (str, srchstr, rplcstr) returns the string 'str' repeated x times.
REVERSE (str) returns the result of REVERSE string str
RIGHT (str, x) returns the rightmost x characters in the str string.
RTRIM (str) returns the space at the end of the str string.
STRCMP (s1, s2) compares strings s1 and s2
TRIM (str) removes all spaces at the beginning and end of the string.
UCASE (str) or UPPER (str) returns the result of converting all characters in the str string to uppercase.

II. Summary
1. ASCII (str)
The return value is the leftmost character value of the str string, that is, the ascii code of the leftmost character.
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.

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

3. BIT_LENGTH (str)
Returns the str length of a binary string.

4. 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.
Convert all parameters into characters and connect them together.

5. CHAR_LENGTH (str)
The return value is the length of the str string, measured in characters.

6. CHARACTER_LENGTH (str)
CHARACTER_LENGTH () is a synonym for CHAR_LENGTH.

7. COMPRESS (string_to_compress)
Compress a string.

8. CONCAT (str1, str2 ,...)
Returns the string generated by the connection parameter.

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

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

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

12. 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 ).

13. FIELD (str, str1, str2, str3 ,...)
The return value is str1, str2, str3 ,...... The str index (location) 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.

14. 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 (that is, the position of str in the strlist ).
A string list is a string consisting of several tokens separated by commas.

15. FORMAT (X, D)
Write the number X format '#,###,###. # ', retain the D-digit after the decimal point and return the result as a string.
If D is 0, the returned result does not contain the decimal point or decimal part.

16. 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 ).

17. INSERT (str, pos, len, newstr) (the characters in str are replaced by 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.

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

19. LCASE (str)
LCASE () is a synonym for LOWER.

20. LEFT (str, len)
Returns the leftmost len character starting with str.

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

22. LOAD_FILE (file_name) (good)
Read the file and return it in string format.
For example, mysql> UPDATE tbl_name
SET blob_column = LOAD_FILE ('/tmp/picture ')
WHERE id = 1;

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

24. 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 ).

25. 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.
Add the padstr with the length of len before str.

26. LTRIM (str)
Returns str, whose leading space character is deleted.

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

28. MID (str, pos, len)
MID (str, pos, len) is a synonym for SUBSTRING (str, pos, len.

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

30. OCTET_LENGTH (str)
OCTET_LENGTH () is a synonym for LENGTH.

31. ORD (str)
If the leftmost character of the string 'str' is a multi-byte character, the code of this character is returned,
If the leftmost character is not a multi-byte character, the ORD () and ASCII () functions return the same value.

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

33. QUOTE (str)
Quote a string to generate a result that can be used as a fully escaped data value in an SQL statement.

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

35. REPLACE (str, from_str, to_str)
Returns the str string and the from_str string replaced by the to_str string.

36. REVERSE (str)
Returns the str string, which is in the opposite order of characters.

37. RIGHT (str, len)
Returns the rightmost len character starting from str.

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

39. RTRIM (str)
Returns the str string, with the trailing space characters deleted.

40. SOUNDEX (str)
Returns a soundex string from str.

41. SPACE (N)
Returns a string consisting of N separators.

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

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

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

45. UCASE (str)
UCASE () is a synonym for UPPER.

46. UNCOMPRESS (string_to_uncompress)
Decompress the string compressed by the COMPRESS () function.

47. UNCOMPRESSED_LENGTH (compressed_string)
Returns the length before compression of the compressed string.

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

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

50. Pattern Matching: Wildcard
% ------- Match any number of characters, even including zero characters
_ ------- Only one character can be matched
Escape '/'

51. STRCMP (expr1, expr2)
If all strings are the same, STRCMP () is returned. if the first parameter is smaller than the second according to the current classification order,-1 is returned. otherwise, 1 is returned.

Note: Tips
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 the comparison.
Mysql connection string is different from SQL


In the past, "+" was used to connect strings with SQL Server. Currently, the database uses mysql to write an SQL statement that accumulates two field values. the SQL statement does not support "+". after a long time, I checked it online, + In mysql is the number addition operation, and the connection string is the CONCAT function.
Example:
Mysql> select CONCAT ('My, '_', '20140901 ');
-> 'My _ 000000'