Like operator in Sql Server

Hello friends, in this article we will learn Sql Server Like and wildcard matching.

Like operator in Sql Used to find a string or substring, which matches a specified pattern or regular characters.

A pattern can consists of regular characters and wildcard characters.

In pattern matching, character data string must exactly match with the characters specified in the regular characters or wildcard characters, which are used to match with fragments of the character string.

wildcard characters used with LIKE operator with WHERE Clause. Which makes it more flexible than = or != operators.

Syntax:

match_expression [NOT] LIKE pattern [ESCAPE escape_character]

match_expression:

match_expression is any valid expression of character data type.

escape_character:

escape_character is a character put in front of a wildcard character, to indicate that, the wildcard is interpreted as a regular character and not as a wildcard.

pattern:

Pattern is the specific string of characters to search for in match_expression. it can be % or _ or [] or [^]. pattern can be a maximum of 8,000 bytes.

To understand more about pattern lets take example from AdventureWorks database.

Create a new view Person.vwPersonName

CREATE View Person.vwPersonName AS SELECT FirstName+' '+coalesce(MiddleName+' ','')+coalesce(LastName+' ','') PersonName,FirstName,MiddleName,LastName FROM Person.Person Go

1) '%' it will search any string of zero or more characters.

SELECT TOP 10 * FROM Person.vwPersonName WHERE PersonName LIKE '%' Go

It will find top 10 PersonName with same result like without where clause.

SELECT TOP 10 * FROM Person.vwPersonName Go

Output: PersonName FirstName MiddleName LastName ------------------------------ ----------------- ------------------ ----------- A. Francesca Leonetti A. Francesca Leonetti A. Scott Wright A. Scott Wright A. Scott Wright A. Scott NULL Wright Aaron Alexander Aaron NULL Alexander Aaron Bryant Aaron NULL Bryant Aaron Butler Aaron NULL Butler Aaron Chen Aaron NULL Chen Aaron Coleman Aaron NULL Coleman Aaron Con Aaron NULL Con Aaron Con Aaron NULL Con (10 rows affected)

2) '%con' Will find top 10 Person record(s) having LastName ends with con.

SELECT TOP 10 * FROM Person.vwPersonName WHERE LastName LIKE '%con' Go

Output: PersonName FirstName MiddleName LastName -------------------------- -------------- ------------------ ---------- Aaron Con Aaron NULL Con Aaron Con Aaron NULL Con Dan Bacon Dan NULL Bacon Dan K Bacon Dan K Bacon (4 rows affected)

3) '%con%' will finds all Person record(s) with the word 'con' anywhere in the PersonName.

SELECT TOP 10 * FROM Person.vwPersonName WHERE PersonName LIKE '%con%' Go

Output: PersonName FirstName MiddleName LastName ----------------------------- ----------------------- -------------------- ------------ Aaron Con Aaron NULL Con Aaron Con Aaron NULL Con Amy R. Consentino Amy R. Consentino Connie Morgan Connie NULL Morgan Connie Rai Connie NULL Rai Connie Vrettos Connie NULL Vrettos Connie L. Coffman Connie L. Coffman Connie R Liang Connie R Liang Connor Adams Connor NULL Adams Connor Allen Connor NULL Allen (10 rows affected)

4) '_' finds all one-letter MiddleName(A,B,C,D and so on.)

SELECT TOP 10 * FROM Person.vwPersonName WHERE MiddleName LIKE '_' Go

Output: PersonName FirstName MiddleName LastName --------------------------- ------------------- ------------------- ---------- Aaron A Allen Aaron A Allen Aaron A Hayes Aaron A Hayes Aaron A Zhang Aaron A Zhang Aaron B Adams Aaron B Adams Aaron C Campbell Aaron C Campbell Aaron C Diaz Aaron C Diaz Aaron C Scott Aaron C Scott Aaron C Yang Aaron C Yang Aaron E Baker Aaron E Baker Aaron E Evans Aaron E Evans (10 rows affected)

5) '__' finds all Two-letter MiddleName(M.,J.,L.,A., and so on.)

SELECT TOP 10 * FROM Person.vwPersonName WHERE MiddleName LIKE '__' Go

Output: PersonName FirstName MiddleName LastName ------------------------------------ ----------------- ------------------- ----------- Abe M. Tramel Abe M. Tramel Abigail J. Gonzalez Abigail J. Gonzalez Abraham L. Swearengin Abraham L. Swearengin Adam J. Reynolds Adam J. Reynolds Alan L. Monitor Alan L. Monitor Albert A. Rhodes Albert A. Rhodes Alberto F. Baltazar Alberto F. Baltazar Aldeen J. Gallagher Aldeen J. Gallagher Alexander J. Berger Alexander J. Berger Alexander J. Deborde Alexander J. Deborde (10 rows affected)

6) '_oe' finds all three-letter FirstName that end with oe (Joe).

SELECT TOP 10 * FROM Person.vwPersonName WHERE FirstName LIKE '_oe' Go

Output: PersonName FirstName MiddleName LastName ------------------------------ -------------------- --------------- ------------- Joe Ashe Joe NULL Ashe Joe Blanco Joe NULL Blanco Joe Gill Joe NULL Gill Joe Gutierrez Joe NULL Gutierrez Joe Hernandez Joe NULL Hernandez Joe Howard Joe NULL Howard Joe Jimenez Joe NULL Jimenez Joe Kim Joe NULL Kim Joe Madan Joe NULL Madan Joe Malhotra Joe NULL Malhotra (10 rows affected)

7) '__e' finds all three-letter FirstName that end with e (Abe, Joe).

SELECT TOP 10 * FROM Person.vwPersonName WHERE FirstName LIKE '__e' Go

Output: PersonName FirstName MiddleName LastName ------------------------ ---------------- --------------- ----------- Abe M. Tramel Abe M. Tramel Jae B Pak Jae B Pak Joe Ashe Joe NULL Ashe Joe Blanco Joe NULL Blanco Joe Gill Joe NULL Gill Joe Gutierrez Joe NULL Gutierrez Joe Hernandez Joe NULL Hernandez Joe Howard Joe NULL Howard Joe Jimenez Joe NULL Jimenez Joe Kim Joe NULL Kim (10 rows affected)

8) [] Any single character within the specified range ([a-f]) or set ([abcdef]).

SELECT TOP 10 * FROM Person.vwPersonName WHERE MiddleName LIKE '[a-f]' Go

Output: PersonName FirstName MiddleName LastName ------------------------ ---------------- --------------- ------------ Aaron A Allen Aaron A Allen Aaron A Hayes Aaron A Hayes Aaron A Zhang Aaron A Zhang Aaron B Adams Aaron B Adams Aaron C Campbell Aaron C Campbell Aaron C Diaz Aaron C Diaz Aaron C Scott Aaron C Scott Aaron C Yang Aaron C Yang Aaron E Baker Aaron E Baker Aaron E Evans Aaron E Evans (10 rows affected)

9) '[g-s]ay' WHERE LastName LIKE '[g-s]ay' finds LastName ending with ay and starting with any single character between g and s, for example Hay, Nay, Ray, and so on.

SELECT TOP 10 * FROM Person.vwPersonName WHERE LastName LIKE '[g-s]ay' Go

Output: PersonName FirstName MiddleName LastName -------------------------- ------------------ ---------------- -------------- Bobby Ray Bobby NULL Ray Chelsea R Ray Chelsea R Ray Harold A Ray Harold A Ray Jeff Hay Jeff NULL Hay Jeff V Hay Jeff V Hay Lorraine Nay Lorraine NULL Nay Lorraine Nay Lorraine NULL Nay Lorraine O Nay Lorraine O Nay Michael S Ray Michael S Ray Michael Sean Ray Michael Sean Ray (10 rows affected)

10) '[^a-e]' Any single character not within the specified range ([^a-e]) or set ([^abcde]).

SELECT TOP 10 * FROM Person.vwPersonName WHERE MiddleName LIKE '[^a-e]' Go

Output: PersonName FirstName MiddleName LastName ------------------------- ---------------- ---------------- ------------ Aaron J Carter Aaron J Carter Aaron J Hughes Aaron J Hughes Aaron J McDonald Aaron J McDonald Aaron J Sharma Aaron J Sharma Aaron K Hall Aaron K Hall Aaron L King Aaron L King Aaron L Perez Aaron L Perez Aaron L Washington Aaron L Washington Aaron L Wright Aaron L Wright Aaron M Gonzalez Aaron M Gonzalez (10 rows affected)

If you have any query or question or topic on which, we might have to write an article for your interest or any kind of suggestion regarding this post, Just feel free to write us, by hit add comment button below or contact via Contact Us form.


Your feedback and suggestions will be highly appreciated. Also try to leave comments from your valid verified email account, so that we can respond you quickly.

 
 

{{c.Content}}

Comment By: {{c.Author}}  On:   {{c.CreatedDate|date:'dd/MM/yyyy'}} / Reply


Categories