MySQL string and string comparison Function Learning

In the mysql database, strings play an important role in the database. Next I will introduce some basic learning about MySQL string comparison functions. If you need them, please refer to them.

In the mysql database, strings play an important role in the database. Next I will introduce some basic learning about MySQL string comparison functions. If you need them, please refer to them.

1: String concatenation Function

The Code is as follows:
CONCAT (str1, str2 ,...)

Returns the string generated by the connection parameter. If any parameter is 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 independent variable contains any binary string, the result is a binary string. A numeric parameter is converted to an equivalent binary string format. To avoid this, you can use an explicit cast, for example: select concat (CAST (int_col as char ), char_col)

The following is a code snippet:

The Code is as follows:
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 concat:

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.

The following is a code snippet:

The Code is as follows:
Mysql> SELECT CONCAT_WS (',', 'First name', 'second name', 'last name '); -> 'First name, Second name, Last name' Mysql> SELECT CONCAT_WS (',', 'First name', NULL, 'last name '); -> 'First name, Last name'

CONCAT_WS () does not ignore any null strings. (However, all NULL values are ignored ).

This function is similar to php's explode and implode, list, and extract. If you are interested, please refer to the php manual.

2: String cut Function

INSERT (str, pos, len, newstr)

This function, mysql Chinese manual translation is too bad, please do it yourself:

This function returns a string:

A: The str string starts from the position pos (starts from 1) (including the position pos), and the subsequent pos bit is replaced by the string newstr;
B: If the pos is longer than the str length, it is connected from the end of str;
C: If len exceeds the length after the remainder, the position after pos (including the nth pos position) will be replaced;
D: If pos is negative, return str;
E: If len is negative, it is the same as the length after len exceeds the remainder, which is different from the function such as php substr.

The following is a code snippet:

The Code is as follows:
Mysql> select insert ('quadratic ', 3, 4, 'wh '); -> 'Quwhattic' Mysql> select insert ('quadratic ',-1, 4, 'wh '); -> 'Quadratic' Mysql> select insert ('quadratic ', 3,100, 'wh '); -> 'Quwh'

This function supports multi-byte characters.

3: hexadecimal conversion function

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. If to_base is a negative number, N is considered as a signed number. Otherwise, N is considered as the unsigned number. The CONV () operation precision is 64 bits.

The following is a code snippet:

The Code is as follows:
Mysql> select conv ('A', 16, 2 ); -> '123' Mysql> select conv ('6e', 18, 8 ); -> '123' Mysql> select conv (-17,10,-18 ); -> '-H' Mysql> select conv (10 + '10' + '10' + 0xa, 10, 10 ); -> '40'

See PHP's:

Base_convert-convert a number between any hexadecimal values

Note:
String base_convert (string $ number, int $ frombase, int $ tobase)
Returns a string containing the number in tobase format. The number is specified by frombase in hexadecimal notation. Both frombase and tobase can only be between 2 and 36 (including 2 and 36 ). Numbers higher than decimal digits are represented by the letter a-z. For example, a represents 10, B represents 11, and z represents 35.

Another MySQL hexadecimal conversion function is a hexadecimal function:

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

If N_OR_S is a string, the return value is a hexadecimal string of N_OR_S. Each character in N_OR_S is converted to two hexadecimal numbers.

The following is a code snippet:

The Code is as follows:
Mysql & gt; select hex (255 ); -> 'Ff' Mysql> SELECT 0x616263; -> 'Abc' Mysql> select hex ('abc '); -> 616263

4: character search and locating Function

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. ELT () is the complement of FIELD.

The following is a code snippet:

The Code is as follows:
Mysql> select elt (1, 'ej', 'heja ', 'hej', 'foo '); -> 'Ej' Mysql> select elt (4, 'ej', 'heja ', 'hej', 'foo '); -> 'Foo'

When I saw this function, I thought it was mainly used for logical judgment, for example:

The following is a code snippet:

The Code is as follows:
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)

However, the following functions make it clear that this is not the case:

FIELD (str, str1, str2, str3 ,...)

The return value is str1, str2, str3 ,...... Str index 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.

If str is NULL, the return value is 0 because NULL cannot be compared with any value. FIELD () is the complement of ELT.

The following is a code snippet:

The Code is as follows:
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.

Let's look at this in the lower part. We can't help but beat it. It's just the in clause:

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. A string list is a string consisting of several tokens separated by commas. If the first parameter is a constant string and the second parameter is the type SET column, the FIND_IN_SET () function is optimized and computed in bits. If str is not in strlist or strlist is a null string, the return value is 0. If any parameter is NULL, the return value is NULL. This function cannot run normally when the first parameter contains a comma.

The following is a code snippet:

The Code is as follows:
Mysql> SELECT FIND_IN_SET ('B', 'a, B, c, D '); -> 2

5: Number Formatting Function

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.

The following is a code snippet:

The Code is as follows:
Mysql> select format (12332.123456, 4 ); -> '123' Mysql> select format (12332.1, 4 ); -> '123' Mysql> select format (12332.2, 0 ); -> '123'

There are four rules for string comparison:

1: MySQL automatically converts numbers to strings, and vice versa.
2: To explicitly convert a number into a string, use the CAST () or CONCAT () function.
3: If a binary string is specified as a parameter for a string function, the resulting string is also a binary string.
4: Generally, if any expression in the string comparison is case sensitive, the comparison is case sensitive.

The following describes the three types of learning:

I. Use the like clause

Expr LIKE pat [ESCAPE 'escape-char ']
Use a simple SQL Regular Expression for comparison. Returns 1 (TRUE) or 0 (FALSE ). If either expr or pat is NULL, the result is NULL.

The mode does not need to be a text string. For example, it can be specified as a string expression or table column.

In the mode, you can use the following two wildcards with LIKE:

Character
Description

%
Match any number of characters, even including zero characters

_
Only one character can be matched

The following is a code snippet:

The Code is as follows:
Mysql> SELECT 'David! 'Like 'David _'; -> 1 Mysql> SELECT 'David! 'LIKE' % D % v % '; -> 1

To check the text of a wildcard, place the escape character before 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 one '%' character

_
Match A '_' character

The following is a code snippet:

The Code is as follows:
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 snippet:

The Code is as follows:
Mysql> SELECT 'David _ 'LIKE 'David | _ 'escape' | '; -> 1

The following two statements show that strings are case insensitive unless one of the operands is a binary string:

The following is a code snippet:

The Code is as follows:
Mysql> SELECT 'abc' LIKE 'abc '; -> 1 Mysql> SELECT 'abc' like binary 'abc '; -> 0

In MySQL, LIKE can appear in numeric expressions. (This is an extension of standard SQL LIKE ).

The following is a code snippet:

The Code is as follows:
Mysql> SELECT 10 LIKE '1% '; -> 1

Note: because MySQL uses the C escape syntax in the string (for example, 'n' represents a newline character), the ''double write used must be used in the LIKE string. For example, to search for 'N', you must write it as '\ n '. To search for '', you must write it as '\' because the backslash is stripped once by the syntax analysis program, it will be stripped once again, and finally there will be a backslash symbol to accept matching.

Ii. Use the like clause of the Regular Expression

Expr REGEXP pat expr RLIKE pat
Execute the pattern matching of the string expression expr and pattern pat. This mode can be extended to a regular expression. The regular expression syntax is discussed in detail in appendix G: MySQL regular expression. If expr matches pat, 1 is returned; otherwise, 0 is returned. If either expr or pat is NULL, the result is NULL. RLIKE is a synonym for REGEXP and serves to provide compatibility for mSQL.

The mode does not need to be a text string. For example, it can be specified as a string expression or table column.

Note: because MySQL uses the C escape syntax (for example, 'n' to represent the newline character) in the string, ''dual writes must be used in the REGEXP string.

REGEXP is case-insensitive unless it is used in the same binary string.

The following is a code snippet:

The Code is as follows:
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 the character type, REGEXP and RLIKE use the current character set (cp1252 Latin1 by default ). Warning these operators do not support multi-byte characters.

Iii. Use Function: STRCMP

STRCMP (expr1, expr2)
If all strings are the same, 0 is returned. If the first parameter is smaller than the second parameter based on the current classification order,-1 is returned. Otherwise, 1 is returned.

The following is a code snippet:

The Code is as follows:
Mysql> select strcmp ('text', 'text2 '); ->-1 Mysql> select strcmp ('text2', 'text '); -> 1 Mysql> select strcmp ('text', 'text '); -> 0

During comparison, STRCMP () uses the current character set. This makes the default comparison case sensitive, except when one or both of the operands are binary strings.

Note: The first two operations are reversed. See

The Code is as follows:
Expr not like pat [ESCAPE 'escape-char '] This is equivalent to NOT (expr LIKE pat [ESCAPE 'escape-char ']). Expr not regexp pat expr not rlike pat This is equivalent to NOT (expr REGEXP pat ).