MySQL string and string comparison function learning

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