Mysql string processing function detailed introduction, summary _mysql

First, concise summary
ASCII value of ASCII (char) return character
Bit_length (str) returns the bit length of the string
CONCAT (S1,S2...,SN) connects S1,s2...,sn to a string
Concat_ws (SEP,S1,S2...,SN) connects the S1,S2...,SN to a string and uses the Sep character interval
INSERT (STR,X,Y,INSTR) begins string str from position x, and the substring of y characters is replaced with string InStr, which returns the result
Find_in_set (str,list) analyzes a comma-delimited list of lists and, if STR is found, returns the position of STR in the list
LCASE (str) or lower (str) Returns the result of changing all characters in the string str to lowercase
Left (str,x) returns the leftmost X character in the string str
LENGTH (s) returns the number of characters in the string str
LTRIM (str) cuts the opening space from the string str
POSITION (SUBSTR,STR) returns the substring substr the first occurrence in string str
QUOTE (str) escapes single quotes in Str with a backslash

REPEAT (STR,SRCHSTR,RPLCSTR) Returns the result of the string str repeats x times
REVERSE (str) Returns the result of the reversed string str
Right (STR,X) returns the rightmost X character in the string str
RTRIM (str) Returns the space of the string str tail
STRCMP (S1,S2) comparison string S1 and S2
TRIM (str) removes all whitespace from the header and tail of the string
UCASE (str) or upper (str) returns the result of converting all characters in the string str to uppercase

Second, the detailed summary
1.ASCII (str)
Returns the value of the leftmost character of String str, which is the ASCII code that obtains the leftmost character.
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 ranging from 0 to 255.

2.BIN (N)
Returns a string representation of a binary value of n that is converted to binary.
Where N is a longlong (BIGINT) number. This equates to CONV (n,10,2). If n is null, the return value is null.

3.bit_length (str)
Returns the string str length of the binary value.

4.CHAR (N,... [USING CharSet])
CHAR () understands each parameter n as an integer that returns a string containing the character given by the code value of these integers. Null value is omitted.
All parameters are concatenated together after they are converted to characters.

5.char_length (str)
The return value is the length of the string str, and the length of the unit is a character.

6.character_length (str)
Character_length () is a synonym for Char_length ().

7.COMPRESS (string_to_compress)
Compresses a string.

8.CONCAT (STR1,STR2,...)
Returns a string resulting from a connection parameter.

9.concat_ws (SEPARATOR,STR1,STR2,...)
Concat_ws () represents CONCAT with Separator and is a special form of CONCAT ().
The first parameter is the separator for the other arguments. The position of the separator is placed between the two strings to which you want to connect.
The delimiter can be a string, or it can be another parameter. If the delimiter is null, the result is null. function ignores NULL values after any of the delimiter parameters.

10.CONV (N,from_base,to_base)
Converts numbers between different numbers of bases. The return value is the N-string representation of the number, which is converted from the From_base base to the To_base base. If any one of the arguments is NULL, the return value is null.
The argument N is understood to be an integer, but can be specified as an integer or a string. The minimum cardinality is 2, and the maximum base is 36.

11.ELT (N,STR1,STR2,STR3,...)
If n = 1, the return value is str1, and if n = 2, the return value is str2, and so on. If n is less than 1 or greater than the number of arguments, the return value is NULL.

12.export_set (Bits,on,off[,separator[,number_of_bits]])
The return value is a string in which an on string is obtained for each bit group in the bits value, and a off string is obtained for each clear 0 bit bit.
Bit values in bits are tested in Right-to-left order (from low bit to high bit).
strings are separated by separate strings (the default is comma ', ') and are added to the results in Left-to-right order. Number_of_bits will give the number of bits tested (default 64).

13.FIELD (STR,STR1,STR2,STR3,...)
The return value is STR1, str2, Str3,...... The str exponent (position) in the list. When STR is not found, the return value is 0.
If all parameters for field () are strings, all parameters are compared by string.
If all the arguments are numbers, compare them by number. Otherwise, the parameters are compared in double.

14.find_in_set (Str,strlist)
If string str is in the strlist of a string list of n chains, the return value ranges from 1 to N (that is, the position of STR in strlist).
A list of strings is a string of strings that are separated by a number of ', ' symbols.

15.FORMAT (X,D)
Writes the number x format as ' #,###,###.## ', preserves the D-bit after the decimal point by rounding, and returns the result as a string.
If D is 0, the return result does not have a decimal point, or does not contain a decimal part.

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

17.INSERT (STR,POS,LEN,NEWSTR) (characters in Str are replaced by NEWSTR)
Returns the string str whose substring starts at the POS position and the Len character that has long been replaced by the string newstr.
If the POS exceeds the string length, the return value is the original string.
If Len is longer than the length of the other string, it is replaced from position pos. If any of the arguments are null, the return value is null.

18.INSTR (STR,SUBSTR)
Returns the first occurrence of a string of str neutron strings. This is the same as locate (), unless the order of the parameters is reversed.

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

20.LEFT (Str,len)
Returns the leftmost character of Len starting from the string str.

21.LENGTH (str)
The return value is the length of the string str, in bytes. A multibyte character counts as multiple bytes.
This means that for a string containing 5 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)
Reads the file and returns the file in the form of a string.
Cases:

Copy Code code as follows:

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 of the string substr of the str neutron string.
The second syntax returns the first occurrence of the string str neutron string substr, starting at the Pos point. If SUBSTR is not in Str, the return value is 0.

24.LOWER (str)
Returns the string str and all characters that have changed to lowercase from the latest Character Set mapping table (default is cp1252 Latin1).

25.LPAD (STR,LEN,PADSTR)
Returns the string str, whose left side is filled by the string padstr to the Len character length. If the length of STR is greater than Len, the return value is shortened to the Len character.
That is, add the length len to the PADSTR in front of str.

26.LTRIM (str)
Returns the string str, whose boot space character is deleted.

27.make_set (BITS,STR1,STR2,...)
Returns a set value (a string containing a character string separated by ', '), consisting of a string that has a corresponding bit in the BITS group.
STR1 corresponds to bit 0, str2 corresponds to bit 1, and so on. STR1, str2, ... The null value in 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 representation of the octal value of N, where N is a longlong (BIGINT) number. This equates 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 String Str is a multibyte character, the code for that character is returned.
If the leftmost character is not a multibyte character, then ORD () and function ASCII () return the same value.

32.POSITION (substr in str)
POSITION (substr in str) is a synonym for LOCATE (SUBSTR,STR).

33.QUOTE (str)
Cites a string that produces a result that can be used as a full escape data value in an SQL statement.

34.REPEAT (Str,count)
Returns a string consisting of repeated string str, with the number of string str equal to count.
If Count <= 0, an empty string is returned. If STR or count is NULL, NULL is returned.

35.REPLACE (STR,FROM_STR,TO_STR)
Returns the string str and all string from_str replaced by the string to_str.

36.REVERSE (str)
Returns the string str, in reverse order and character order.

37.RIGHT (Str,len)
Starts with the string str and returns the most right Len character.

38.RPAD (STR,LEN,PADSTR)
Returns the string str, whose right side is filled by a string padstr to Len character length.
If the length of the string str is greater than Len, the return value is shortened to the same length as the Len character.

39.RTRIM (str)
Returns the string str, and the trailing whitespace character is deleted.

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

41.SPACE (N)
Returns a string consisting of n-interval symbols.

42.SUBSTRING (Str,pos), SUBSTRING (str from POS) SUBSTRING (Str,pos,len), SUBSTRING (str from POS for Len)
SUBSTR () is a synonym for SUBSTRING ().
The format without the Len parameter returns a substring from string str, starting at position pos.
Format with len parameter returns a substring of the same length as Len character from String str, starting at position pos.
Use the from format as the standard SQL syntax. It is also possible to use a negative value for the POS.
If so, the position of the substring starts at the POS character at the end of the string, not at the beginning of the string.

43.substring_index (Str,delim,count)
Returns the string str from the string, before the delimiter Delim and Count appears.
If Count is positive, it returns everything to the left of the final delimiter (starting from the left). If count is negative, all content on the right side of the delimiter (starting from the right) is returned.

44.TRIM ([{BOTH | Leading | Trailing} [REMSTR] from] str) TRIM (remstr from] str)
Returns the string str, where all remstr prefixes and/or suffixes have been deleted.
If none of the classifier both, leadin, or trailing is given, it is assumed to be both. REMSTR is optional and can be deleted without specifying a space.

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

46.UNCOMPRESS (string_to_uncompress)
The compressed string after the compress () function is decompressed.

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

48.UNHEX (str)
Performs a reverse operation from Hex (str). This means that each pair of hexadecimal digits in the argument is interpreted as a number and converted to the character represented by that number.
The resulting character is returned as a binary string.

49.UPPER (str)
Returns the string str and the character converted to uppercase letters based on the latest character set mappings (default is cp1252 Latin1).

50. Pattern matching: wildcard characters
%-------Match any number of characters, even including 0 characters
_-------can only match one character
Escape with '/'

51.STRCMP (EXPR1,EXPR2)
If all strings are the same, return strcmp (), if the first argument is less than the second according to the current sorting order, return-1, and other cases return 1.

Attached: Small tips
MySQL automatically transforms numbers into strings when necessary, and vice versa:
Mysql> Select 1+ "1"; -> 2mysql> SELECT CONCAT (2, ' test '); -> ' 2 test '
If you want to explicitly transform a number into a string, pass it as a parameter to Concat ().
If the string function provides a binary string as a parameter, the resulting string is also a binary string. A number that is transformed into a string is treated as a binary string. This affects only the comparison.
MySQL connection string is different from SQL


Previously used SQL Server connection string is used "+", now the database with MySQL, write an accumulation of two field values SQL statement incredibly does not support "+", depressed for half a day in the online check, just know MySQL in the + is the number added operation, the connection string is Concat function
Cases:
Mysql> Select CONCAT (' My ', ' _ ', ' 2001 ');
-> ' my_2001 '