Use fuzzy query directives in SQL Server like

Read Catalogue

• Brief introduction
• First, the general search conditions used like
• Second, like special case: Search wildcard characters

Back to top introduction

Like in sql: Use a good fuzzy query directive in SQL Server like;

Query is an important function in SQL Server, and in the query will like to use, you can search for some unexpected results and effects, like the magic is that it provides the fuzzy query function, by matching it with four kinds of wildcard characters, like put the role of the finishing touches in the condition.

Back to top one, general search conditions with the like

The data types that can be paired with like are primarily string, date, or time values. The LIKE keyword can use a regular expression to contain the above three types of data, which can contain any combination of the following four wildcard characters.

wildcard meaning

% any string that contains 0 or more characters.

_ (underline) any single character.

[] Specifies any single character within a range (for example [a-f]) or a collection (for example [abcdef]).

[^] Any single character that is not within a specified range (for example [^a-f]) or a collection (for example [^abcdef]).

Enclose the wildcard character and string in single quotation marks.

For example:

Like ' Zhao% ' will search for the surname Zhao's name or the Chinese character ' Zhao ' beginning of the string (such as Zhao Gang, Zhao Xiaogang, etc.).

Like '% just ' will search for all strings (such as Liu Gang, Li Xiaogang, etc.) that end with the Chinese character ' just '.

Like '% small ' will search all strings that contain ' small ' characters in any location (such as Zhao Xiaogang, Li Xiaogang, Shan Ben Xiaolong, etc.).

Like ' _ Xiao Gang ' will search for the names of all three Chinese characters (such as Li Xiaogang, Zhao Xiaogang) ending with the Chinese character "Xiao Gang".

For English, the situation is more, such as

Like ' [Ck]ars[eo]n ' will search for the following strings: Carsen, Karsen, Carson, and Karson (such as Carson).

Like ' [M-z]inger ' will search for all names (such as Ringer) ending with the string inger, starting with any single letter from M to Z.

Like ' m[^c]% ' begins the search with the letter M, and the second letter is not all names of C (such as Macfeather).

For example, the database in pubs has a table author, its phone number is a list of the phone, then find all the area code 010 Phone number command is:

SELECT  phonefrom    pubs.dbo.authorsWHERE   phone like ' 10% ';

You can use the same wildcard character with a not-like. To find all phone numbers for which the area code is not 010 in the authors table, use any of the following equivalence queries:

SELECT  phonefrom    pubs.dbo.authorsWHERE   phone not like ' 10% ';

-OR

SELECT  phonefrom    pubs.dbo.authorsWHERE not   phone like ' 10% ';

The is not NULL clause can be used in conjunction with wildcards and like clauses. For example, the following query retrieves all the phone numbers that start with 010 and are not NULL from the authors table:

SELECT  phonefrom    authorswhere   phone like ' 010% ' and phone are not        NULL;

Important The output of statements that contain the LIKE keyword depends on the sort order that is selected during installation.

The WHERE condition that is available for the text column is only like, is NULL, or PATINDEX.

Wildcards that are not used with like are interpreted as constants rather than patterns, in other words, the wildcard characters represent only their own values. The following query attempts to find a phone number that consists of less than four characters 10%. The query does not find a phone number that starts with 010.

SELECT  phonefrom    pubs.dbo.authorsWHERE   phone = ' 10% ';

Another problem that you should focus on when using wildcards is the impact on performance. If an expression starts with a wildcard, the index cannot be used. (As with the name "%mith" rather than "Smith", you won't know which page of the phone book to start looking for.) The wildcard character at the middle or end of an expression does not prevent the use of the index, as in the phone book, if the name is "samuel%", you should know where to start, regardless of whether Samuels and Samuelson are on the phone book.

Back to top second, like special case: Search wildcard characters

The above search can be for ordinary Chinese characters or Chinese, if you encounter the above four kinds of wildcard characters to be searched, how should be handled? The first thing to note is that wildcard characters can be searched, and there are two ways to specify characters that are normally used as wildcards:

Use the Escape keyword to define an escape character. In a pattern, the wildcard is interpreted as a normal character when the escape character is placed before the wildcard character. For example, to search for a string that contains the string 5% anywhere, use:

WHERE ColumnA like '%5/%% ' ESCAPE '/'

In the LIKE clause above, the leading and ending percent sign (%) interpreted as a wildcard character, and a slash (/) followed by a percent semicolon is interpreted as a char%.

Only the wildcard character itself is included in square brackets ([]). To search for a dash (-) instead of specifying a search scope with it, specify the dash as the first character in square brackets:

WHERE ColumnA like ' 9[-]5 '

The following table shows the use of wildcard characters enclosed in square brackets.

Symbolic meaning

Like ' 5[%] ' 5%

A string like ' 5% '--5 followed by 0 or more characters

Like ' [_]n ' _n

Like ' _n ' A, in, on (and so on)

Like ' [A-CDF] ' A, B, C, D, or F

Like ' [-ACDF] '-, A, C, D, or F

Like ' [[] ' [

Like '] '

If you use like for string comparisons, all characters in the pattern string, including the starting space and/or trailing spaces, make sense. If the query comparison requires that all rows containing "ABC" (with a space after ABC) be returned, the column value will not be returned as "ABC" (No space after ABC) row. But the reverse is not the case. You can ignore the trailing spaces in the expression that the pattern is to match. If the query comparison requires that all rows containing "ABC" (with no spaces after ABC) be returned, all rows that begin with "ABC" and have 0 or more trailing spaces are returned.

If you think reading this blog gives you something to gain, you might want to click " recommend " in the lower right corner.

If you want to find my new blog more easily, click on " Follow me "

If you want to give me more encouragement, you may wish to click on the right, " enjoy your stay"

Blog is his own summary of the results of learning, learning to summarize the knowledge-"analysis of the problem-" to solve the problem.

Where the views/descriptions in the text are incorrect, please correct them.

Thank you for your reading, if you are interested in the content of my blog, please continue to follow my following blog, I am yxtic.

Use fuzzy query directives in SQL Server like