Common SQL Server fuzzy query methods

Pattern Matching in search criteria
The like keyword searches for the string, date, or time value that matches the specified pattern. The like keyword uses the regular expression to include the pattern to be matched by the value. The mode contains the string to be searched. A string can contain any combination of four wildcard characters.
Wildcard characters
% Any string containing zero or more characters.
_ Any single character.
[] Any single character in the specified range (for example, [a-f]) or set (for example, [abcdef.
[^] Any single character that is not in the specified range (for example, [^ a-f]) or set (for example, [^ abcdef.

Use single quotes to enclose wildcards and strings. For example:
Like 'mc % 'searches all strings starting with MC (for example, mcbadden ).

Like '% Inger' searches all strings ending with the letter Inger (such as ringer and Stringer ).

Like '% en %' searches all strings (such as Bennet, green, and mcbadden) that contain letters en at any position ).

Like '_ heryl' searches for names of all six letters ending with heryl, such as Cheryl and Sheryl ).

Like '[Ck] ARS [EO] n' will search for the following strings: Carsen, Karsen, Carson, and karson (such as Carson ).

Like '[M-Z] Inger' searches for all names (such as ringer) ending with string Inger, starting with any single letter from m to Z ).

Like'm [^ C] %' searches for all names (such as macfeather) whose names start with M and whose second letter is not C ).
In the authors table, query all the telephone numbers with the partition code 415:
Select phone
From pubs. DBO. Authors
Where phone like '20140901'
Not like can be used with the same wildcard. To search for all phone numbers whose area code is not 415 in the authors table, use any of the following equivalent queries:CopyCodeThe Code is as follows: Select phone
From pubs. DBO. Authors
Where phone not like '000000'
-- Or
Select phone
From pubs. DBO. Authors
Where not phone like '000000'

The is not null clause can be used with wildcards and like clauses. For example, the following query Retrieves all phone numbers starting with 415 and is not null from the authors table:Copy codeThe Code is as follows: Use pubs
Select phone
From authors
Where phone like '200' and phone is not null

The output result of a statement that contains the like keyword depends on the sorting order selected during installation.

The where condition that can be used for text columns is like, is null, or patindex.
wildcards not used with like are interpreted as constants rather than schemas. In other words, these wildcards only represent their own values. The following query attempts to find a phone number that consists of only four characters, 415%. This query does not look for phone numbers starting with 415.
Select phone
from pubs. DBO. Authors
where phone = '000000'
another issue to be considered when using wildcards is the impact on performance. If the expression starts with a wildcard, the index cannot be used. (Given the name "% mith" instead of "Smith", you cannot find the page from the phone book .) Wildcards in the middle or end of an expression do not affect the use of indexes. Just like in a phone book, if the name is "Samuel %", whether or not both of them are in the phone book, you should know where to start searching.
Search for wildcard characters
you can search for wildcard characters. You can use either of the following methods to specify characters that are typically used as wildcards:
use the escape keyword to define escape characters. In mode, when an escape character is placed before a wildcard, the wildcard is interpreted as a common character. For example, to search for strings containing string 5% at any position, use:
where Columna like '% 5/%' escape '/'
In the like clause above, the leading and ending percent signs (%) are interpreted as wildcards, the percent sign after the slash (/) is interpreted as the character %.
square brackets ([]) only contain wildcards. To search for a break number (-) instead of specifying the search range, specify the break number as the first character in square brackets:
where Columna like '9 [-] 5'
The following table shows the usage of wildcards enclosed in square brackets.
Symbolic Meaning
like '5 [%] '5%
like '5%' 5 followed by a string of 0 or more characters
like '[ _] n' _ n
like '_ n', 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 comparison, all the characters in the mode string, including the start space and/or trailing space, are meaningful. If the query comparison requires that all rows containing "ABC" (there is a space after ABC) be returned, the column value "ABC" (no space after ABC) is not returned. However, this is not the case. Ignore spaces at the end of the expression to be matched by the pattern. If the query comparison requires that all rows containing "ABC" (without spaces after ABC) be returned, all rows starting with "ABC" with zero or multiple trailing spaces are returned.