In this blog post we will learn various tips related to Querying Special Characters with the help of wildcard in SQL Server.
Querying Special Characters
Some special characters can be tricky to pattern match since they themselves can represent different values at different times. Let’s look at some examples. Here is a quick look at all the records in the [Grant] table of the JProCo database. Note: Since [Grant] is also a keyword it must be enclosed in square brackets or double quotes to designate it as the [Grant] table and now the keyword. Take a look at many of the names in the Grant Name field and notice we have many names with special symbols in them. See figure below:
Finding literal % signs with wildcards
We learned about special characters in yesterdays post called wildcards. When using the percentage sign % or the underscore _ we can do relative searches. We have a grant called “92 Purr_Scents %% team” which has a percentage symbol in the name. We have other grants with percentages in their names. How do you search for a percentage sign with two wildcards on either side? It would appear to SQL that you’re looking for three wildcards as seen in the query below:
--Bad query pattern logic (finds all Grant records) SELECT * FROM [GRANT] WHERE GrantName LIKE '%%%'
We have three special characters and no literal percent symbol. Help is on the way again with the square brackets. Take the wild card you want to use as a literal percentage symbol and surround it with square brackets. You see two grants having a percentage symbol within their names. In this example the square brackets give you the literal percentage symbol. In this figure you see just the two grants that have a % sign in the name.
Finding literal _ signs with wildcards
You may know that the underscore is also a wildcard. We can use this to find a specific second letter. How many Grants have the letter A for the second letter can be found with the following query:
--Find Grants where A is the 2nd letter. SELECT * FROM [GRANT] WHERE GrantName LIKE '_A%'
| GrantID | GrantName | EmpID | Amount |
| 6 | TALTA_Kishan International | 3 | 18100 |
| 10 | Call Mom @Com | 5 | 750 |
In this example by asking for one character before the letter A and any amount afterward, we names like “TALTA_Kishan International” and “Call Mom @Com”. The % symbol wildcard can represent many characters while the _ symbol wildcard always represents exactly one.
To find the Grants with underscores in the name we do the same technique we used with the % wildcard. Again, we take the wildcard that you want to evaluate and put it in square brackets.
You see three grants having underscores in their names (Figure 2.20). In this example the square brackets tell SQL you are looking for a literal underscore character.
Note: If you want to setup the sample JProCo database on your system you can watch this video.
Question 2:
Q 2.) You want to find all grant names that have an Underscore as the second letter. Which SQL code would you use?
- SELECT * FROM [Grant] WHERE GrantName like ‘_[_]% ‘
- SELECT * FROM [Grant] WHERE GrantName like ‘[_]_% ‘
- SELECT * FROM [Grant] WHERE GrantName like ‘_%[_]%_ ‘
- SELECT * FROM [Grant] WHERE GrantName = ‘_[_]% ‘
- SELECT * FROM [Grant] WHERE GrantName = ‘[_]_% ‘
- SELECT * FROM [Grant] WHERE GrantName = ‘_%[_]%_ ‘
Please post your answer in the comment section to win Joes 2 Pros books.
Rules:
Please leave your answer in the comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from the United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my Facebook page for winners name and correct answer.
Winner will get Joes 2 Pros Volume 1.
The contest is open till next blog post shows up at which is next day GTM+2.5.
Reference: Pinal Dave (https://blog.sqlauthority.com)
186 Comments. Leave new
Hi Pinal Dave,
The correct answer is number 1
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Kind regards
Andy
Leeds, UK
Option No. 1 is the one
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Thanks
San Salvador (El Salvador)
option 1 ……….SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
manoj from Delhi, India
Option 1 is correct answer:
This is because underscore is a wildcard character which signifies a single character and then matches underscore as the second letter.
From USA.
Option 1
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Question 2:
Q 2.) You want to find all grant names that have and Underscore as the second letter. Which SQL code would you use?
Answer:
1.SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Answerer: Sivaprasad S
Hi Pinal sir,
Thank you for proving extended features of wildcards.
The correct answer for this question is option no. 1:
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Reasons: We have to find all grant names as a second letter.
Since underscore is also one of the wildcard used to match single character and we also have to find underscore as letter.
It is very much clear that is you want to find any special character inside the string simply enclosed that letter into character class.
By enclosing wildcard letter into character class it loose it special meaning and treated as ordinary character.
We have used correct keyword like and _ as first letter bcoz we have to find second letter as underscore so there must be only one letter before underscore and _ wildcard is used to match only single character.
Also we have to find _ underscore as second letter and its is wildcard so it must be enclosed into character class i.e.[] and it may be followed by any number of character.
Following options are not correct because:
Option 2:
SELECT * FROM [Grant] WHERE GrantName like ‘[_]_% ‘
Expression is not in correct sequence.
Option 3:
SELECT * FROM [Grant] WHERE GrantName like ‘_%[_]%_ ‘
Incorrect pattern.
Option 4:
SELECT * FROM [Grant] WHERE GrantName = ‘_[_]% ‘
Like must be used instead if = symbol.
Option 5:
SELECT * FROM [Grant] WHERE GrantName = ‘[_]_% ‘
Expression is not in correct sequence and Like must be used instead if = symbol.
Option 6:
SELECT * FROM [Grant] WHERE GrantName = ‘_%[_]%_ ‘
Incorrect pattern and Like must be used instead if = symbol.
Thanks
Chirag Satasiya(Mumbai – INDIA)
Answer : 1
City: Mumbai
Country: India
Correct option [1]
Ritesh(India)
Answer :
1. SELECT * FROM [Grant] WHERE GrantName like ‘_[_]% ‘
First character is valid for any character so _ is right and we want second one as _ so we enclose it in []-square braces.
Name: Paurav
Country: India
Answer is Option 1: SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Country India
Ritesh
Correct option [1]
Ritesh
Vadodara
(India)
correct option-1
India
Shilpa Sharma
Hi Pinal,
This is a day late, but the correct answer is the first choice:
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Thanks for the quiz and the knowledge.
Best Regards,
Bill Pepping
(United States participant)
The answer is #1 “SELECT * FROM [Grant] WHERE GrantName like ‘_[_]%’
#4,5 and 6 all have the “=” so they are out as they will not return what we are wanting
#2 puts the “_” as the first letter
#3 allows there to be more than one letter before the “_”
Deb from the USA
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Options 1 :)
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
USA
Option Number 1 seems good to me.
United States
1. SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
This is the best suitable answer as the first ‘_’ specifies that anything can be the first character. The second ‘_’ withing square brackets specifies that SQL Server only searches for _ as the second letter.
2. SELECT * FROM [Grant]
WHERE GrantName like ‘[_]_% ‘
This will not work as it will return any thing as the second letter and not ‘_’ as second letter as expected.
3. SELECT * FROM [Grant]
WHERE GrantName like ‘_%[_]%_ ‘
This will not work as it will return any thing as the second letter and not ‘_’ as second letter as expected.
Below options will not return as expected due to the presence of the ‘=’ operator
SELECT * FROM [Grant]
WHERE GrantName = ‘_[_]% ‘
SELECT * FROM [Grant]
WHERE GrantName = ‘[_]_% ‘
SELECT * FROM [Grant]
WHERE GrantName = ‘_%[_]%_ ‘
Answer 1 : SELECT * FROM [Grant] WHERE GrantName like ‘_[_]% ‘