Home > T-SQL > Wildcard (%, _, [],[^]) in SQL Server

Wildcard (%, _, [],[^]) in SQL Server


Wildcard characters are used in the where clause to search a string with some patterns. for instance, checking a name that starts with ‘Mic’ and rest of the characters may be anything. In this scenario we can use wildcard characters to represent patterns in the search string.

There are four type of wildcard character available in SQL Server. We have to use LIKE or PATINDEX keyword to use pattern matching with wildcard characters.

%  is used to represent anything before, after or whole string.

Example:

use AdventureWorks

select * from Production.Product where Name like '%'  -- Anything
select * from Production.Product where Name like 'Bl%' -- Starts with Bl and rest anything
select * from Production.Product where Name like '%Bl%' -- anything before Bl and after Bl

[] is used to search single character within a range (A-Z or 0-9) or a single character in the pattern matching.

use AdventureWorks

--searching a string  'SA-M' with any number range from 0 to 9 and anything after the number
select * from Production.Product where ProductNumber like 'SA-M[0-9]%'

/*Searching a sting 'LI-' and next character must be 1 and followed by any numeric 0 to 9 and must followed by 0 and anything
select * from Production.Product where ProductNumber like 'LI-[1][0-9][0]%'

[^] is used to search for a string without the character given in the square bracket after ^symbol and in specified position.

use AdventureWorks

--searching for a string start with 'A' and the second character is not 'B' and anything
select * from Person.CountryRegion where Name like 'A[^b]%'

_ (underscore) is used to match a single character pattern match in a string.

use AdventureWorks

--searching for a string start with any character and the second character is  'm' and rest anything
select * from Person.CountryRegion where Name like '_m%'

Learn New functions in SQL Server 2012. Thanks for reading.

Advertisements
  1. Mitch
    November 19, 2011 at 10:14 am

    Thanks. Very good training!

  2. Chris Beecy
    June 6, 2012 at 2:12 pm

    Great Job! Thanks!!

  3. March 2, 2013 at 12:31 pm

    Thanks Chris

  4. Sathish
    August 28, 2013 at 12:50 am

    I too echo the same as others saying thanks!! Simple and superb

  5. Brahmananda Rao
    November 15, 2013 at 5:17 am

    How to search for a name starts with “IND_AP”? it should not return a row which start with “IND AP”.

    • November 15, 2013 at 11:15 am

      Space is also a character. if you don’t want to consider space in you search then you can use character range [].

      Sample query
      SELECT * FROM dbo.names WHERE [Column0] LIKE ‘IND[a-b,A-Z]AP’

  6. Michael
    December 27, 2013 at 1:39 pm

    I have a sql statement that does a job between 2 tables. If I use a where clause like “where state like ‘%'” I get fewer results than if I do not have the where clause at all. What’s the difference? I wouldn’t think there would be any.

    • Michael
      December 27, 2013 at 1:40 pm

      sorry, does a “join” between 2 tables

      • January 10, 2014 at 5:42 pm

        No problem. Thanks for your reply with answer.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: