Implementation of the difference between regexp and like in sql

1、REGEXP

• use: Advanced string matching, using regular expressions.
• Features: High flexibility and can perform complex pattern matching (such as beginning, ending, character set, etc.).
• Basic syntax：

SELECT column_name FROM table_name WHERE column_name REGEXP 'pattern';

Among them, column_name is the column name to be matched, table_name is the table name to be queryed, and pattern is the regular expression pattern to be matched.

• Regular expression elements：

Here are some common regular expression elements and their meanings:

• .: Match any single character (except line breaks).
• ^: Match the start position of the string.
• $: Match the end position of the string.
• []: Specify a character collection to match any character in it. For example,[abc]matcha、borc。
• [^]: Specify a mismatched character collection. For example,[^abc]Missinga、borc。
• |: Logical "or" operator, matching the left pattern or the right pattern. For example,pattern1|pattern2matchpattern1orpattern2。
• (): Used to combine patterns so that they are treated as a whole.
• *: Match the previous pattern zero or multiple times.
• +: Match the previous pattern once or more times.
• ?: Match the previous pattern zero or once.
• {n}: Match the previous pattern exactly n times.
• {n,}: Match the previous pattern at least n times.
• {n,m}: Match the previous pattern at least n times and no more than m times.

Example of usage:

Find lines containing specific words：

SELECT column_name FROM table_name WHERE column_name REGEXP 'a';

Find lines that start with a specific word：

SELECT column_name FROM table_name WHERE column_name REGEXP '^a';

Find lines that end with a specific word：

SELECT column_name FROM table_name WHERE column_name REGEXP 'a$';

Find lines containing specific characters：

SELECT column_name FROM table_name WHERE column_name REGEXP '[abc]';

Find lines that do not contain a specific set of characters：

SELECT column_name FROM table_name WHERE column_name REGEXP '[^abc]';

Find rows containing one of multiple patterns：

SELECT column_name FROM table_name WHERE column_name REGEXP 'pattern1|pattern2';

2、LIKE

• use: Simple string matching, using wildcard characters%and_。

• Features: Good performance and suitable for simple pattern matching (such as including a certain character).

• Basic syntax：

SELECT column_name FROM table_name WHERE column_name LIKE pattern;

in,patternCan contain text characters,%(Represents any number of characters, including zero characters) and_(Represents any character).

Example of usage:

Find lines containing specific text：

Find outcolumn_nameAll lines containing "text" are included, you can write it like this:

SELECT * FROM table_name WHERE column_name LIKE '%a%';

Find lines that start with a specific text：

If you want to find outcolumn_nameAll lines starting with "prefix", you can write this:

SELECT * FROM table_name WHERE column_name LIKE 'a%';

Find lines ending in a specific text(Notice:LIKEIt does not directly support matching endings in specific text, but can be implemented by inverting strings and patterns):

For example, in MySQL, you can useREVERSE()Functions (if available) invert strings and patterns and then match:

SELECT * FROM table_name WHERE REVERSE(column_name) LIKE REVERSE('%a');

This method may not be supported by all database systems, and may not be as good as using it directly.REGEXPor other database-specific functions are efficient.

Find lines that contain specific characters (but not any characters)：

If you want to searchcolumn_nameThe second character in  is all lines of “a”, you can use_To represent the first arbitrary character, followed by "a":

SELECT * FROM table_name WHERE column_name LIKE '_a%';

Notes:

• LIKEMatches are case sensitive unless the database or query is set to case insensitive (it depends on the implementation and configuration of the database).
• use%and_Be careful when you are as they can significantly affect the performance of the query, especially when they appear at the beginning of the pattern. Because the database needs to scan more rows to find matches.
• Consider usingREGEXPReplacementLIKE, to obtain more powerful matching capabilities and flexibility. but,REGEXPProbably moreLIKESlower, especially when processing large amounts of data.

3. Differences and choices

• performance：LIKEUsually faster,REGEXPIt may be slower when dealing with complex modes.
• flexibility：REGEXPhigher,LIKESuitable for simple matching.
• choose: Choose according to your needs, for complex matchingREGEXP, for simple matchingLIKE。

This is the end of this article about the difference between regexp and like in SQL. For more related regexp and like content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!