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
The correct answer is Option 1:
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Option 4, 5, and 6 use the ‘=’ operator and would not work because they would find an exact match. The wild cards are only valid in a LIKE statement. Option 2 gives us any record with the underscore as the first character (and looks like bad sql because the second character is a wildcard and is unnecessary since there is a %. Option 3 is also bad sql but would find any value that contains an underscore. Option 1 uses the _ for the wild card in the first character position, looks for the literal underscore in the second position and then any value afterwards.
Country of Residence: USA
Correct answer is
1: SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
first _ is to say we are looking for 2 character for pattern matching and
[_] is to say we are looking for _ as matching character.
Then % for characters on wards.
Ghanshyam Patel
Ahmedabad
India
very helpful article. Option 1 is the correct answer. sounds like something i should consider putting into constraints sometimes too.
The answer is #1
from USA
Replying after 1st reply on a public forum; can not be any fun. The answer should go to someother place and then result ( with winnder with explanation) to be published here on this blog. Besides, seeing all 146 answers and scrolling for life, with the same reply, ditto copies , will not add value
Answer : Option 1
Wildcards (%) only work with the LIKE keyword so (4), (5) and (6) are all incorrect. LIKE ‘[_]_% ‘ would only match strings where the first character is an _ and has at least one letter after that so (2) is also wrong. The correct answer is (1) because the first _ forces one character of any type and the second one (in square brackets) must be a literal underscore.
A.Arul Prakash
Country : USA
The correct answer is 1.
First _ tell sql we are looking for second letter.
Second _ with [] tell sql that _ is the character we are looking for.
Country – Canada
The Correct answer is
Option 1: SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
and nice have quiz like this
Country – India
The correct answer is Option 1:
1.SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Anil (India)
Correct Answer is Option#01
Azhar Iqbal
From Pakistan.
The Option 1 is the correct answer
1.SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Hemant
(India)
Answer #1
1. SELECT * FROM [Grant] WHERE GrantName like ‘_[_]% ‘
Country India
1.SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]%’
Hello guys,
I am suffering a problem with this.
I want to find procedures list which involes the object [MA].[Employee]
SELECT name,object_definition(object_id)
FROM sys.procedures
WHERE object_definition(object_id) like ‘%[MA].[Employee]%’
But it is not working properly.
can anu one give a solution for this
Thanks & Regards
Srikanth Nallamothu
Answer #1 is correct.
SELECT * FROM [Grant] WHERE GrantName like ‘_[_]%’
Here _ will skip 1st character & [_] will pick all the GrantNames whose 2nd character is _.
Dheerendra Pandey
India(New Delhi)
1.SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]%’
is the answer
The option 1 is correct,
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Since _ (underscore) should come at 2nd Place & its a special character.
Option 1 is the right one
Select * from [Grant]
where GrantName like ‘_[_]% ‘
Francisco,
Miami
Correct answer is
Option 1: SELECT * FROM [Grant] WHERE GrantName like ‘_[_]% ‘
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
My Country is Canada