MySQL string processing function detailed Introduction, summary

  This article mainly introduced the MySQL string processing function detailed introduction, the summary, needs the friend to be possible to refer to under

First, concise summary ASCII (char) return character ASCII code value bit_length (str) returns the bit length of the string CONCAT (S1,S2...,SN)   connects S1,s2...,sn to a string Conca T_ws (SEP,S1,S2...,SN) connects S1,s2...,sn to a string, and with the Sep character interval INSERT (STR,X,Y,INSTR), the string str begins at position x, and the substring of y characters is replaced with the string InStr, which returns the result Find_　　　　　　　　In_set (str,list) analyzes a comma-delimited list of lists, if STR is found, returns the position of STR in the list LCASE (str) or lower (str) returns the result left (str,x) that changes all characters in the string str to lowercase Returns the leftmost X character LENGTH (s) in the string str returns the number of characters in the string str LTRIM (str) cuts the opening space POSITION (SUBSTR,STR) from the string str to return the substring substr in the word The first occurrence of a string str QUOTE (str) escapes the single quotation mark   REPEAT (STR,SRCHSTR,RPLCSTR) in the backslash escape str to return a string str repeat x results REVERSE (str) return to the top 　　　　　　　　　Inverted string str results right (STR,X) returns the rightmost X character in the string str RTRIM (str) returns the string str trailing space STRCMP (S1,S2) comparison string s1 and S2 TRIM (str) Remove all space UCASE (str) or upper (str) from the header and tail of the string returns the result of converting all characters in string str to uppercase   II, detailed summary 1.ASCII (str) Returns the value of the leftmost character of string str, i.e. The ASCII code with 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)   The string representation of the binary value of N, which 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 binary string str length of the 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 synonymous with char_length ().   7.COMPRESS (string_to_compress)   compress 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, which is a special form of CONCAT ().    The first argument is a separator for other parameters. 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)   Conversion of numbers between different 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 parameters, 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 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,...)   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 string list strlist consisting 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, replace it from position pos. If any of the arguments are null, the return value is null.   18.INSTR (str,subSTR)   Returns the first occurrence of the string str neutron string. 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 string str.   21.LENGTH (str)   The return value is the length of 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) reads the file and returns the file in the form of a string.   Example:     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 str neutron string substr. 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)   return string str and all characters that have changed to lowercase according to 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 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 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 synonymous with 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 synonymous with LENGTH ().   31.ORD (str)   If the leftmost character of String Str is a multi-byte character, the code that returns the character,  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)   return string str, in reverse order and character order.   37.RIGHT (Str,len)   begins with string str, returning the most right Len character.   38.RPAD (STR,LEN,PADSTR) return 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)   return string str, trailing whitespace character 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)   Subs TR () 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 from format as 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 from String str 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 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)   characters compressed by compress () functionString for decompression.   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%      -------        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 one, according to the current sorting order, returns-1, and the other returns 1.       attachment: Small hint MySQL automatically converts numbers to 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 to 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 with SQL different     previously used SQL Server connection string is using "+", now database with MySQL, write an additive 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 operation of the number addition, the connection string is the CONCAT function example: mysql> select CONCAT (' My ', ' _ ', ' 2001 '); -> ' my_2001 '