1: String Join function
The code is as follows |
Copy Code |
CONCAT (STR1,STR2,...) |
Returns a string resulting from a connection parameter. If any of the arguments are 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 argument contains any twos feed string, the result is a binary string. A numeric parameter is converted to an equal binary string format; To avoid this, you can use explicit type cast, for example: SELECT CONCAT (CAST (Int_col as CHAR), Char_col)
The following is a code fragment:
The code is as follows |
Copy Code |
mysql> SELECT CONCAT (' My ', ' S ', ' QL '); -> ' MySQL ' mysql> SELECT CONCAT (' My ', NULL, ' QL '); -> NULL Mysql> SELECT CONCAT (14.3); -> ' 14.3 ' |
The following concat_ws is a variant of the concat:
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.
The following is a code fragment:
The code is as follows |
Copy Code |
mysql> SELECT concat_ws (', ', ' ' name ', ' Second name ', ' last Name '); -> ' Name,second name,last name ' mysql> SELECT concat_ws (', ', ' ' name ', ' NULL ', ' last Name '); -> ' name,last name ' |
CONCAT_WS () does not ignore any empty strings. (All NULL is ignored, however).
This function is similar to PHP's explode and implode, list, and extract. Interested can look at the PHP manual.
2: String splicing function
INSERT (STR,POS,LEN,NEWSTR)
This function, the MySQL Chinese manual translation is too bad, or I do it myself:
This function returns a string:
The A:STR string starts at the position POS (counting from 1), and the POS bit is replaced by the string newstr;
B: If the POS is greater than the length of STR, then the convergence begins from the back of STR;
C: If Len exceeds the length after the rest, the position of POS (including the POS position) are all replaced;
D: If the POS is negative, return str;
E: If Len is a negative number, the same length as Len exceeds the rest, which is not the same as PHP's substr function.
The following is a code fragment:
The code is as follows |
Copy Code |
mysql> SELECT INSERT (' Quadratic ', 3, 4, ' What '); -> ' quwhattic ' mysql> SELECT INSERT (' quadratic ',-1, 4, ' What '); -> ' quadratic ' mysql> SELECT INSERT (' Quadratic ', 3, ' What '); -> ' Quwhat ' |
This function supports multibyte characters.
3: The Feed conversion function
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. If To_base is a negative number, then N is treated as a signed count. Otherwise, N is considered an unsigned number. The CONV () operates at a precision of 64 bits.
The following is a code fragment:
The code is as follows |
Copy Code |
mysql> SELECT CONV (' A ', 16,2); -> ' 1010 ' mysql> SELECT CONV (' 6E ', 18,8); -> ' 172 ' Mysql> SELECT CONV ( -17,10,-18); -> '-h ' Mysql> SELECT CONV (10+ ' + ' + ' +0xa,10,10); -> ' 40 '
|
Please see the PHP:
Base_convert-converts numbers between arbitrary systems
Description
String Base_convert (string $number, int $frombase, int $tobase)
Returns a string containing the representation of number in tobase. The number itself is specified by Frombase. Frombase and Tobase are only between 2 and 36 (including 2 and 36). Numbers above decimal are represented by a letter A-Z, for example a denotes 10,b 11 and Z represents 35.
The MySQL conversion function also has one, which is a function defined for the 16 binary:
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).
If n_or_s is a string, the return value is a n_or_s hexadecimal string representation, where each character in each n_or_s is converted to two hexadecimal digits.
The following is a code fragment:
The code is as follows |
Copy Code |
mysql> SELECT HEX (255); -> ' FF ' Mysql> SELECT 0x616263; -> ' abc ' mysql> SELECT HEX (' abc '); -> 616263
|
4: Character Lookup locator function
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. ELT () is the complement of FIELD ().
The following is a code fragment:
The code is as follows |
Copy Code |
Mysql> SELECT ELT (1, ' ej ', ' Heja ', ' Hej ', ' foo '); -> ' EJ ' Mysql> SELECT ELT (4, ' ej ', ' Heja ', ' Hej ', ' foo '); -> ' foo '
|
See the above function, also think the main logical judgment used, such as:
The following is a code fragment:
code is as follows |
copy code |
mysql> SELECT ELT (if (1, 2, 3), ' EJ ', ' Heja ', ' Hej ', ' foo '); +------------------------ ----------------------+ | ELT (if (1, 2, 3), ' EJ ', ' Heja ', ' Hej ', ' foo ') | +----------------------------------------------+ | heja | +----------------------------------------------+ 1 row in Set (0.00 sec) |
But when you look at the following function, you know more than that:
FIELD (STR,STR1,STR2,STR3,...)
The return value is STR1, str2, Str3,...... The STR index 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.
If STR is NULL, the return value is 0, because null cannot be compared equally with any value. FIELD () is the complement of ELT ().
The following is a code fragment:
The code is as follows |
Copy Code |
mysql> SELECT FIELD (' ej ', ' Hej ', ' ej ', ' Heja ', ' Hej ', ' foo '); -> 2 mysql> SELECT FIELD (' fo ', ' Hej ', ' ej ', ' Heja ', ' Hej ', ' foo '); -> 0
|
This function is similar to Array_search and Array_key_exists, In_array, and Isset in PHP.
Another look at this, can not help astounding, is directly in the clause:
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. A list of strings is a string of strings that are separated by a number of ', ' symbols. If the first argument is a constant string, and the second is the Type SET column, the Find_in_set () function is optimized, using bit computations. If STR is not strlist or strlist is an empty string, the return value is 0. If any of the arguments are null, the return value is null. This function will not work correctly when the first argument contains a comma (', ').
The following is a code fragment:
The code is as follows |
Copy Code |
mysql> SELECT find_in_set (' B ', ' a,b,c,d '); -> 2
|
5: Number Format function
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.
The following is a code fragment:
The code is as follows |
Copy Code |
Mysql> SELECT FORMAT (12332.123456, 4); -> ' 12,332.1235 ' Mysql> SELECT FORMAT (12332.1,4); -> ' 12,332.1000 ' Mysql> SELECT FORMAT (12332.2,0); -> ' 12,332 '
|
The following four rules are in string comparisons:
1:mysql automatically converts numbers to strings and vice versa.
2: You can use the CAST () or the CONCAT () function if you want to explicitly convert numbers to strings.
3: If a string function has been given a binary string as a parameter, the resulting string is also a binary string.
4: In general, if any of the expressions in string comparisons are case-sensitive, the comparison is case sensitive.
The following three kinds of learning according to the above mentioned:
One: Use the LIKE clause
Expr like Pat [ESCAPE ' Escape-char ']
pattern matching, using SQL simple regular expression comparisons. Returns 1 (TRUE) or 0 (FALSE). If either expr or PAT is null, the result is null.
The pattern does not need to be a literal string. For example, you can be specified as a string expression or a table column.
The following two wildcard characters can be used with like in the pattern:
Character
Description
%
Matches any number of characters, even including 0 characters
_
Only one character can be matched
The following is a code fragment:
The code is as follows |
Copy Code |
mysql> SELECT ' david! ' Like ' david_ '; -> 1 mysql> SELECT ' david! ' Like '%d%v% '; -> 1
|
To verify the text instance of the wildcard character, you can place the escape characters in front of the character. If the escape character is not specified, it is assumed to be '. This is the same as the general programming language.
String
Description
%
Match a '% ' character
_
Match an ' _ ' character
The following is a code fragment:
The code is as follows |
Copy Code |
mysql> SELECT ' david! ' Like ' david_ '; -> 0 mysql> SELECT ' david_ ' like ' david_ '; -> 1
|
You can also specify a different escape character, using the Escape statement:
The following is a code fragment:
The code is as follows |
Copy Code |
mysql> SELECT ' david_ ' like ' david|_ ' ESCAPE ' | '; -> 1
|
The following two statements illustrate that string comparisons are case-insensitive, unless one of the operands is a binary string:
The following is a code fragment:
The code is as follows |
Copy Code |
mysql> SELECT ' abc ' like ' abc '; -> 1 mysql> SELECT ' abc ' like BINARY ' abc '; -> 0 |
In MySQL, like allows you to appear in a numeric expression. (This is an extension of standard SQL like).
The following is a code fragment:
The code is as follows |
Copy Code |
Mysql> SELECT like ' 1% '; -> 1
|
Note: Because MySQL uses the C escape syntax in a string (for example, ' n ' represents a newline character), in the like string, you must use the ' double write '. For example, to find ' n ', you must write it ' \ n '. To find ', you must write it as ' \ \ ' Because the backslash is stripped by the parser, and when the pattern is matched, it is stripped once, and then a backslash is left to accept the match.
Two: Using the LIKE clause of the regular expression
Expr REGEXP Pat Expr rlike Pat
Performs pattern matching for string expression expr and pattern pat. The pattern can be extended as a regular expression. The syntax of regular expressions is discussed in detail in the Appendix G:mysql regular Expressions. If expr matches Pat, it returns 1; otherwise return 0. If either expr or PAT is null, the result is null. Rlike is synonymous with regexp, which provides compatibility for mSQL.
The pattern does not need to be a literal string. For example, you can be specified as a string expression or a table column.
Note: Because in a string, MySQL uses the C escape syntax (for example, ' n ' to represent a newline character), the ' double write ' must be used in the regexp string.
REGEXP is case-insensitive unless it is used in conjunction with a binary string.
The following is a code fragment:
code is as follows |
copy code |
mysql> SELECT ' monty! ' REGEXP ' m%y%% '; -> 0 mysql> SELECT ' monty! ' REGEXP '. * '; -> 1 mysql> SELECT ' new*n*line ' REGEXP ' new\*.\*line '; -> 1 Mysql> SELECT ' A ' REGEXP ' a ', ' a ' REGEXP BINARY ' a '; -> 1 0 Mysql> SELECT ' a ' REGEXP ' ^[a-d] '; -> 1 |
When determining character types, REGEXP and rlike use the current character set (default is cp1252 Latin1). Warning: These operators do not support multibyte characters.
Three: Use function: STRCMP
STRCMP (EXPR1,EXPR2)
Returns 0 if all the strings are the same, or 1 if the first argument is less than the second according to the current sort order, and returns 1.
The following is a code fragment:
The code is as follows |
Copy Code |
mysql> SELECT STRCMP (' text ', ' Text2 '); ->-1 mysql> SELECT STRCMP (' Text2 ', ' text '); -> 1 mysql> SELECT STRCMP (' text ', ' text '); -> 0 |
When a comparison is performed, STRCMP () uses the current character set. This makes the default comparison case-sensitive, except when one or two of the operands are binary strings.
Description: For the first two, there is a reverse operation. See next
code is as follows |
copy code |
Expr Like Pat [ESCAPE ' Escape-char '] This is equivalent to not (expr-like Pat [ESCA PE ' Escape-char ']). Expr not REGEXP Pat expr rlike Pat This is equivalent to not (expr R Egexp Pat). |