In-depth understanding of MySQL string comparison functions

We often use MySQL string comparison functions. The following describes the usage of MySQL string comparison functions in detail and hopes to enlighten you in learning MySQL string comparison functions.

MySQL automatically converts numbers to strings, and vice versa.

Mysql> SELECT 1 + ''1'';-> 2 mysql> select concat (2, ''test'');-> ''2 test''

To explicitly convert a number into a string, use the CAST () or CONCAT () function:

Mysql> SELECT 38.8, CAST (38.8 as char);-> 38.8, ''38. 8 ''mysql> SELECT 38.8, CONCAT (38.8);-> 38.8, ''38. 8''

CAST () is preferred.

If you have given a binary string as a parameter to a string function, the resulting string is also a binary string. A number converted to a string is treated as a binary string. This will only affect the comparison results.

Generally, if any expression in the string comparison is case sensitive, the comparison is case sensitive.

◆ 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:

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 '\'.

Mysql> SELECT ''David! ''Like ''david \ _ '';-> 0 mysql> SELECT ''david _'' LIKE ''david \ _ '';-> 1

To specify a different ESCAPE character, use the ESCAPE statement:

Mysql> SELECT ''david _ ''LIKE ''david | _ ''escape'' | '';-> 1

The escape sequence can be null or a character length. From MySQL 5.1.2, if NO_BACKSLASH_ESCAPES SQL mode is activated, the sequence cannot be empty.

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

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

Mysql> SELECT 10 LIKE ''1% '';-> 1

Note: because MySQL uses the C escape syntax in a string (for example, ''to represent a newline character), the '\' pair must be used in the LIKE string. For example, to search for '', you must write it ''. To search for '\', you must write it as '\'. The reason is that the backslash is stripped once by the syntax analysis program, during pattern matching, It is stripped once again, and a backslash symbol is left to accept the matching.

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

◆ 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 syntax of regular expressions is described in Appendix G: MySQL Regular Expressions in detail. 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, ''to represent line breaks) in a string, the '\' pair must be used in the REGEXP string.

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

Mysql> SELECT ''Monty! ''Regexp ''m % y %'';-> 0 mysql> SELECT ''Monty! ''Regexp ''. * '';-> 1 mysql> SELECT ''new ** 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.

◆ STRCMP (expr1, expr2)

If all strings are the same, STRCMP () is returned. If the first parameter is smaller than the second according to the current classification order,-1 is returned. Otherwise, 1 is returned.

Mysql> select strcmp (''text'', ''text'');->-1 mysql> select strcmp (''text'', ''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.

How to query duplicate fields in a MySQL large table

Problem about distinct in mysql query statements

How to Implement MySQL full-text Query

Example of the number of MySQL query results

How to query intermediate records in MySQL