LIKE fuzzy query and REGEXP usage in SQL

This article introduces in detail the usage of LIKE fuzzy query and REGEXP in SQL and some usage of special characters. For more information, see.

This article introduces in detail the usage of LIKE fuzzy query and REGEXP in SQL and some usage of special characters. For more information, see.

When searching for data in a database, the SQL wildcard can replace one or more characters.

The SQL wildcard must be used with the LIKE operator.

In SQL, you can use the following wildcard characters:

Wildcard description

% Replace one or more characters
_ Replace only one character
[Charlist] any single character in the character Column

[^ Charlist]

Or

[! Charlist]

Any single character not in the character Column

-- For SQL fuzzy query, use the like comparison word and add the wildcards in SQL, refer to the following:
-- 1. LIKE 'mc % 'searches all strings starting with Mc (for example, McBadden ).
-- 2. LIKE '% inger' searches all strings ending with the letter inger (such as Ringer and Stringer ).
-- 3. LIKE '% en %' searches for all strings (such as Bennet, Green, and McBadden) containing the letter en at any position ).
-- 4. LIKE '_ heryl' searches for names (such as Cheryl and Sheryl) of all six letters ending with heryl ).
-- 5. LIKE '[CK] ars [eo] n' will search for the following strings: Carsen, Karsen, Carson, and Karson (such as Carson ).
-- 6. LIKE '[M-Z] inger' searches for all names (such as Ringer) ending with string inger and starting with any single letter from M to Z ).
-- 7. LIKE 'M [^ c] %' searches for all names (such as MacFeather) Starting with M, and the second letter is not c ).

We are familiar with the Like statement. For example, to find all users whose usernames contain "c", we can use mydatabase

The Code is as follows:

Select * from table1 where username like '% c %" -- Query data that contains % and ends with A-Z Select top 100 * FROM dbo. TableName WITH (NOLOCK) WHERE colName LIKE '% [A-Z] 'escape'' -- Query data ending with % Select top 100 * FROM dbo. TableName WITH (NOLOCK) WHERE colName LIKE '%' escape'' -- Query data containing 50' Select top 100 * FROM dbo. TableName WITH (NOLOCK) WHERE colName LIKE '% 50 ''%' -- Query data that contains' Select top 100 * FROM dbo. TableName WITH (NOLOCK) WHERE colName LIKE '%' -- Query colValue with underlines Select top 100 * FROM dbo. TableName WITH (NOLOCK) WHERE colName LIKE '% _ %' ESCAPE'' -- Query the colValue containing a broken number -- LIKE '%-% -- %' indicates the first exit character in the break number. The following % is the constant character data value; -- The second break number is the exit character; -- The third break number is the constant character data value. Select top 100 * FROM dbo. TableName WITH (NOLOCK) WHERE colName LIKE '%-% -- % 'escape '-' -- Query the data starting with e instead of a-z Select top 100 * FROM dbo. TableName WITH (NOLOCK) WHERE colName LIKE '[a-z] E' -- Query data starting with tes but not ending with t-e Select top 100 * FROM dbo. TableName WITH (NOLOCK) WHERE colName LIKE 'tes [^ t-e] %' -- Query the colValue of a with the second letter Select top 10 * FROM dbo. TableName WITH (NOLOCK) WHERE colName LIKE '_ A %' -- Query the colValue with the third character A and the length of 20 characters Select top 10 * FROM dbo. TableName WITH (NOLOCK) WHERE colName LIKE '_ ayboy-Naked Sins'

REGEX usage

"." Matches any single character.
A character class "[...]" matches any character in square brackets. For example, "[abc]" matches "a", "B", or "c ". To name a range of characters, use a hyphen (-). "[A-z]" matches any lowercase letter, and "[0-9]" matches any number.
"*" Matches zero or multiple items before it. For example, "x *" matches any number of "x" characters, "[0-9] *" matches any number of numbers, and ". * "matches any number of things.
Regular Expressions are case-sensitive, but if you want to, you can use one character class matching method. For example, "[aA]" matches lowercase or upper-case "a", and "[a-zA-Z]" matches any letter in either of the two statements.
If it appears anywhere in the tested value, the schema matches (as long as they match the entire value, the SQL schema matches ).
To locate a pattern so that it must match the start or end of the tested value, use "^" at the start of the pattern or "$" at the end of the pattern ".
To demonstrate how the extended regular expression works, the LIKE Query shown above is rewritten using REGEXP below:
To find the name starting with "B", use "^" to match the start of the name and use "[bB]" to match "B" in lower case or upper case ":

The Code is as follows:

Mysql> SELECT * FROM pet WHERE name REGEXP "^ [bB]"; + -------- + --------- + ------ + ------------ + | Name | owner | species | sex | birth | death | + -------- + --------- + ------ + ------------ + | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Boane | Diane | dog | m | + -------- + --------- + ------ + ------------ + To find the name ending with "fy", use "$" to match the end Of the name: Mysql> SELECT * FROM pet WHERE name REGEXP "fy $ "; + -------- + --------- + ------ + ------------ + ------- + | Name | owner | species | sex | birth | death | + -------- + --------- + ------ + ------------ + ------- + | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | + -------- + --------- + ------ + ------------ + ------- + To locate the name containing a "w", use "[wW]" to match the "w" in lower case or upper case ": Mysql> SELECT * FROM pet WHERE name REGEXP "[wW]"; + ---------- + ------- + --------- + ------ + ------------ + | Name | owner | species | sex | birth | death | + ---------- + ------- + --------- + ------ + ------------ + | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Boane | Diane | dog | m | | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | + ---------- + ------- + --------- + ------ + ------------ +

Charindex is another method:

The Code is as follows:
Use mydatabase Select * from table1 where charindex ('C', username)> 0

In theory, this method has one more judgment statement than the previous method, that is,> 0, but this judgment process is the fastest, I believe that more than 80% of the operations are spent on searching strings and other operations, so using the charindex function is no big deal. This method also has advantages, that is, %, | and so on can be used directly in this charindex in characters not directly searched with like, as shown below:

The Code is as follows:
Use mydatabase Select * from table1 where charindex ('%', username)> 0

You can also write it as follows:

The Code is as follows:
Use mydatabase Select * from table1 where charindex (char (37), username)> 0

ASCII characters are %

Special characters of SQL Like

For other special characters: '^', '-', ']', because they are used in '[]', they must be escaped in another way, the like clause is introduced, and it is worth noting that escape can escape all special characters.

The Code is as follows:

Select 1 where '^ ABCDE 'like '! ^ ABCDE 'escape '! ' Select 1 where '-ABCDE 'like '! -ABCDE 'escape '! ' Select 1 where '] ABCDE 'like'!] ABCDE 'escape '! ' Select 1 where '% ABCDE' like '% ABCDE 'escape'' Select 1 where '% ABCDE 'like '! % ABCDE 'escape '! ' Select 1 where '% ABCDE 'like' # % ABCDE 'escape '#' Select 1 where '% ABCDE' like '@ % ABCDE 'escape '@' Select 1 where '[ABCDE 'like '! [ABCDE 'escape '! ' Select 1 where '] ABCDE 'like'!] ABCDE 'escape '! '

As you can see, we can use the characters followed by escape as escape characters. The character after escape is equivalent to the escape character ''in the C-language string ''.

Finally, let's look at a more complex match.

The Code is as follows:
Select 1 where '[^ A-Z] ABCDE' like '[^ A-Z] % 'escape''