SQL, the wildcard characters of the LIKE operator (%, _ , [] and ^)

Hi Guys,

Today a quick and easy post to answer a question that was asked to me some time ago.

We will talk about the wildcard characters of the like operator.
In its simplicity I think it is useful to who is entering the world of the SQL language.

Enjoy the reading!

Wildcard characters of the LIKE operator

SQL ANSI LIKE operator has different wildcard characters which are:

• The percent sign %
• The underscore _
• The square braket [^] and []
  

 

Their use is very simple!

 

 

The percent sign (%) wildcard

The percent sign (%) allows you to find if a substring is contained within a string.

 

For example we can write the following query:

SELECT * FROM ITEMS WHERE NAME LIKE '%AD%'

This statements will return all the rows of items table where the field name contain the string "AD" in any position. 

For example:

 

RADIO

BLADE

ADVERTISING

The underscore ( _ ) wildcard

Looking at the following statement the underscore wildcard allows you to find all the rows of the table items where the field name contain the substring "R_CE" where the "_" can be any single character

SELECT * FROM ITEMS WHERE NAME LIKE '%R_CE%'

For example:

RACE

RICE

The bracket square [] and [^] wildcard

Looking at the following statement the bracket square allow you to find all the rows of the table items where the field name contain the substring "R*CE" where * can be only the character "A" or "B".

SELECT * FROM ITEMS WHERE NAME LIKE '%R[AB]CE%'
 

What is contained within the square brackets are characters that you are trying to match on.

So in the following statement we are search the field name for the presence of a substring "R*CE" where * can be a character from A to F.

SELECT * FROM ITEMS WHERE NAME LIKE '%R[A-F]CE%'
  

The character ^ simply means not within.

That'all for today!

Luca

 

Follow me on linkedin if you like theese posts!

Previous post: A bit of theory of databases: The Halloween problem and the Table Spool