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
This will give you the required result
1.
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Hi ,
what about full text search with special characters as search ‘C#’ ???
Please Help Me.
Option 1 is correct answer
1.SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Country: India
Varun
Correct Answer: Option 1: SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Explanation: As underscore is a wildcard character and we can use this to find a specific second letter with the LIKE operation.
Country: India [Noida]
All The best to everyone.. :)
Correct Answer :1
Varinder Sandhu (India)
Option 1 is the right answer.
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Actually we want only one character before “_” sign, so we can’t put “%” there but we have to put “_”, after that we want to find second letter would be “_” but “_” has different meaning with LIKE operator so we have to use square bracket surrounding and after second letter “_”, there could be anything so we are using “%” sign.
Ritesh (Gujarat, India)
Definitely #1: SELECT * FROM [Grant] WHERE GrantName like ‘_[_]% ‘
Country = USA
The correct answer is
—————————-
1. SELECT * FROM [Grant] WHERE GrantName like ‘_[_]% ‘
Reason
———–
The first underscore will match any single character at position 1(one) and the second underscore as enclosed in ‘[]’ will match it exactly as a literal followed by any zero or more characters due to the presence of ‘%’
Correct answer is
1.SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Sonia[Hyderabad-India]
Correct Answer is
1. SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Vishal from India(Delhi)
Thanks for posting this knowledgeable series….
The correct answer is 1st option
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Thanks
Vijayakumar P [Kochi India]
Option 1: SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Thanks,
Kannappan
India
Correct answer is
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]%’
The above Query None of from 6 options.
option 1 have extra space after % . so it wont give expected result(why because it search for space after [_]).
Ranjit.. India,Hyderabad
correct answer is option 1
SELECT * FROM [Grant] WHERE GrantName like ‘_[_]% ‘
because underscore is a wildcard character which signifies a single character and matches Underscore as the second letter.
Name : Bhargav Mistri
Country : India
First option is right answer
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
very nice article and useful too.
Ahmedabad,India.
The Answer is option 1
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Explanation
* The fisrt “_” is telling that the first character can be anyone in the GrantName.
* The “[_]” telling to sql is that the second character should be “_” (underscore)
in the GrantName
* The “%” is telling sql is that remaining can be anything in the GrantName.
Govindaraj, Bangalore, India.
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]%’
Pratik
India
My answer is 1st optuion.
1. SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Reason – You need to put a underscore at the first place to say match 1 character and then escape the literal underscore in the second character followed by any character representation which is %.
Option 1 will be correct answer:
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Thanks Pinal for this wonderful Quiz.
My answer is 1st option.
1. SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Reason – You need to put a underscore at the first place to say match 1 character and then escape the literal underscore in the second character followed by any character representation which is %.