SQL Server Like Predicate


Before discussing LIKE, we will define what a predicate is. The term “predicate” is used to refer to an expression that determines whether something is true or false. Or in other words, it makes an assertion and returns true or false based on that.

LIKE determines whether a specific character string matches a specified pattern. A pattern can include regular characters and wildcard characters. LIKE can be used in the WHERE clause. Here is Microsoft’s documentation on LIKE.

Here are some examples that do not use like.

[EmployeeFistName] = 'Robert'
[EmployeeLastName] = 'Brown'
[Amount] = 45
[EmployeeLastName] > 'Wilson'

Here are some examples of LIKE. The percent symbol means zero or any number of characters. Also know that you can use NOT LIKE as well.

[EmployeeFistName] like 'Robert'  -- matches Robert only
[EmployeeFistName] like 'Robert%'  -- matches Robert, Roberto ...
[EmployeeFistName] like 'R%'  -- starts with R or r
[EmployeeFistName] like '%R'  -- ends with R or r
[EmployeeFistName] like '%w%'  -- has a w anywhere in the string
[EmployeeFistName] like '%wi%'  -- has wi anywhere in the string
[EmployeeFistName] not like '%wi%'  -- does not have wi anywhere in the string

The underscore is used for any single character. It is one and only one character.

[EmployeeFistName] like '_W%'  -- matches W as the second character followed by any number of characters (including no characters) 

The square brackets are used for any range or set of characters.

[EmployeeFistName] like '[r-t]%'  -- starts with r s or t followed by any number of characters
[EmployeeFistName] like '[rst]%'  -- starts with r s or t followed by any number of characters (the same as above)

Any single character not within the specified range.

[EmployeeFistName] like '[^r-t]%'  -- does NOT start with r s or t and is followed by any number of characters
[EmployeeFistName] like '[^rst]%'  -- does NOT start with r s or t and is followed by any number of characters (the same as above)
[EmployeeLastName] like '_[rs]%'  -- has r or s as the second character followed by any number of characters
[EmployeeFirstName] like '[CS]heryl'  -- the first name of Cheryl or Sheryl.

Wildcard Characters as Literals

You can use the wildcard pattern matching characters as literal characters. To use a wildcard character as a literal character, enclose the wildcard character in brackets. These examples are from the Microsoft documentation referenced at the start of this post.

[MyColumn] like '5[%]' -- starts with 5 and is followed by a % character: 5%
[MyColumn] like '[_]n'  -- starts with the underscore character followed by the n
[MyColumn] like '[-acdf]'  -- -, a, c, d, or f
[MyColumn] like '[[]'  -- [
[MyColumn] like ']'  -- ]
[MyColumn] like 'abc[_]d%'  -- abc_d followed by any number of characters

The ESCAPE Clause

To search for the percent sign as a character instead of as a wildcard character, the ESCAPE keyword and escape character must be provided. For example, a sample database contains a column named [Comment] that contains the text 30%. To search for any rows that contain the string 30% anywhere in the comment column, specify a WHERE clause such as:

where Comment like '%30!%%' ESCAPE '!'

Leave a comment

Your email address will not be published. Required fields are marked *