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
Answer is Option 1 :
Because 1st underscore defines that 1st character can be any thing and then [_] defines that it should not be treated as wildcard.
India
Good morning,
I believe that the first one is correct.
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
The reasons are: First, it must use the keyword “like” and not the equal sign. Second, the first underscore lets the first character be anything. Third, the underscore in brackets matches exactly the underscore and finally the wildcard ‘%’ will match anything after that.
I am from the United States.
Best Regards,
Jeffrey
Option 1 – is Correct answer.
Option 2 – This code will find out first char as ‘_’ and 2nd Char will be any thing hence this is incorrect.
Option 3 – In this code 2nd char can be any thing because of % , So this is also incorrect.
Option 4,5,6 – will not work because of ‘ = ‘ in this case.
I am from India.
Thanks and Regards
-Dnyanesh
1. SELECT * FROM [Grant] WHERE GrantName like ‘_[_]% ‘
Country Like ‘USA’
Answer: Option #1
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Reason: The first underscore is a wild card, the second underscore in brackets matches the underscore exactly, and then % will match anything, which is exactly what we want for this query.
Thomas Riehle
USA
answer is #1
SELECT * FROM [Grant] WHERE GrantName like ‘_[_]% ‘
‘_’ underscore with out bracket will read only one character (the first character) without any special comparison ‘[ _ ]’ underscore with bracket will read only the underscore symbol in the second character place and ‘%’ symbol will read any number of character following underscore ‘_’
Place of residence : India
using relational database also we can analys the data
why we are using ssas
Answer Is :
SELECT * FROM [Grant] WHERE GrantName like ‘_[_]% ‘
Jigar badgujar, Country = india
Explanation : As per SQL like syntax first underscore will be consider for any character at the first place and after that undderscore in brackets [_] will surely look for underscore char at the second place as it is placed on second place in like syntax and after that last % will go for any char after second underscore.
Thanks,
Jigar Badgujar
Option 1
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Thanks
Tricia (USA)
Option No:-1 SELECT * FROM [Grant] WHERE GrantName like ‘_[_]% ‘
Country:-India,Ahmedabad
Hi,
Answer 1 is correct because first _ represents one digit then [_] one underscore then % means rest of all.
Thanks…
Rajneesh Verma
Country: INDIA
Opton 1 is the right answer
SELECT * FROM [Grant] WHERE GrantName like ‘_[_]%’
INDIA
The choice would be Option 1
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
the first _ is a wildcard.
The square brackets around the second underscore tells SQL that we are looking for a literal underscore character. The % will match anything after the underscore.
1 is answer no doubt
Correct answer is :
SELECT * FROM [Grant]
WHERE GrantName = ‘_[_]% ‘
Regards,
Nilesh Pawar
India [Mumbai]
sorry
Correct answer is :
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Regards,
Nilesh Pawar
India [Mumbai]
The Correct Answer is option 1
SELECT * FROM [Grant] WHERE GrantName like ‘_[_]% ‘
Why Explanation
1) The first underscore _ is for first character can be anything in first place.
2) The [_] is for the second character should be _ (underscore) it will look for having underscore char in the second place.
3)The Like operator with % in the last is telling SQL SERVER that remaining character can be anything after second underscore.
Dilip Kumar Jena
Country – India
Shree
Answer is :
1.SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]%’
1. The first Underscore will look for any one character.
2. The second underscore with bracket will check for any name having Underscore as the second letter.
3. The % is for any letters followed by underscore.
Thanks,
Shree
Bangalore India.
Hello ,
I believe this is the correct answer
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
The first wildcard symbol _ always represents exactly any one character before or after the desired attribute , in our case as we are looking for the grant names that have Underscore as the second letter, so here we place the first _taking the first place and the second [_] as the second letter taking the second place……
problem solved cheers!!
USA
Option 1:
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
United States