Answer simple quiz at the end of the blog post and -
Every day one winner from India will get Joes 2 Pros Volume 1.
Every day one winner from United States will get Joes 2 Pros Volume 1.
Querying Special Characters
Some special characters can be tricky to pattern match since they themselves can represent different values at different times. Let 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 GrantName 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 wildcard 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 specifc 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 inside 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 comment section to win Joes 2 Pros books.
Rules:
Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from 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 from United States will get Joes 2 Pros Volume 1.
Winner from India will get Joes 2 Pros Volume 1.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.
Reference: Pinal Dave (
http://blog.SQLAuthority.com
)
Correct answer is
Option 1: SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Thanks
Dhananjay Kumar
India [Pune]
[links removed]
The Correct Answer is 1.SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Ramdas
Charlotte,NC USA
WooHoo…. I am the first to answer. My answer is
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 %
2: is matching the literal underscore in the first character.
3: is matching literal character anywhere in the length of the string
4,5,6: dont stand a chance as the where clause is saying to find the grant name as the exact match of the right hand side instead of a pattern matching.
crossing my fingers iWish iWin :)
Thanks pinal for wonderful series.
regards
Lohith
India [Bangalore]
Answer 1
The first _ is worked as a wild card and looking for next letter after anything in first. Second _ is actually a letter as it is covered with [], i.e. [_] and % means any letters from the third. So, _[_]% checks second letter as _ in a given GrantName. For searching a particular letter, we are using either LIKE, PATINDEX or CHARINDEX.
Option 2 is searching Grantname starting with _.
Option 3 is searching GrantName for _ anywhere in the string and same as ‘%_%’
Options 4, 5, 6 are searching for exact match.
Shekhar Teke
Auckland, New Zealand
Hi,
This is Sathya from Chennai, India. The correct answer is option 1. Reason being, it has
1. Like operator
2. Looks for any character as the first letter by mentioning _ inside the the single quote as the first character
3. It searches for exact _ match for thr second letter by specifying [_] as the second character inside the single quotes
4. The resulted word can have any character after that. That’s the reason we are adding a % at the end inside the single quotes
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
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Leo Pius
USA
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
Country – India
Option 1: SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Sandeep, INDIA
Question 2:
Ans : SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Aditya, Chennai INDIA
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
The correct answer is 1st option:
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
we want to look for names which have underscore at the second letter that’s why we have put simple underscore at first position (_ symbol wildcard always represents exactly one) and second underscore in square bracket (which we want to search)
India
Correct answer is Option 1
because underscore is a wildcard character which signifies a single character and matches Underscore as the second letter
INDIA (Pune)
Correct answer is
Option 1: SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Explanation : The underscore is a wildcard character which means a single character and it will matches Underscore as the second letter
Thanks
Prasad Yangamuni
India [Pune]
Option 1: SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Aashish Kumar Sharma,Noida INDIA
The correct answer is option 1.
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Find all grant names that have Underscore as the second letter. The first wildcard character _ searches for a single character. Next identifies the character specified within the [_] square bracket, followed by any number of character which is represented by %.
by
Yasodha.N (India)
Option 3
SELECT * FROM [Grant]
WHERE GrantName = ‘_[_]% ‘
is the right answer.
India
opps i have pasted wrong answer.
Answer :1
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Saravanakumar,
iLink Multitech solutions,
Valasaravakam,
Chennai,
the correct is answer is
option 1 : SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
because underscore will tell you from which character(i mean position) and underscore enclosed in square bracket will tell you that you have to find underscore in that column…
i am from india
The correct answer is :
1.SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Country : India
:)
Pingback: SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority
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 %.
correct answer is
option 1.SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
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 %.
Malay from Ahmedabad,INDIA
Correct answer is Option 1:
SELECT * FROM [Grant] WHERE GrantName like ‘_[_]%’
where first underscore is for any letter in the GrantName and second underscore embrace in [ ] is for underscore character and remaing part can be anything.
Ahmedabad, INDIA
Option 1
SELECT * FROm [GRANT]
WHERE GRANtNAME LIKE ‘_[_]%’
Thanks
Ashish
Answer is option 1
I’m from India
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Correct Answer is option 1.
Correct Answer is : option 1
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Correct Answer : option 1
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
because the first ‘_’ represents a wildcard while the next one act as the charecter we want to search
Thanks
Santosh
India
Nice One …
Answer: Option 1
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]%‘
“%” next empty space needs to remove ….
Option 1 : SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Country : India
Name : Madhivanan
Question 2. Answer is 1. (Country – UK)
2,4,5,6 will give you empty result so that they are not the right answer for question 2.
However… 3 is given you all result with ” which we don’t want. We only want ” as the second character. So answer is 1.
option 1 is the answer
first _ means you are looking for 2nd character specifically
now [_] at 2nd position says we want this character at this position
% says any character 3rd onwards
vishal jindal
country : india
Option 1. SELECT * FROM [Grant] WHERE GrantName LIKE ‘_[_]%’
Name: Sudeepta
Country: India
option 1
ghanshyam
from india (bangalore)
The correct answer is Option-5.
SELECT * FROM Customer
WHERE Firstname like ‘[a-m]%’
OPTION 1 is Correct
India
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
The Correct answer is
Option 1: SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Thanks
Mandar Alawani
Mumbai, India
The correct answer is
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Correct answer is
Option 1: SELECT * FROM [Grant] WHERE GrantName like ‘_[_]%’
Thanks
Kapil Madan
India
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Option 1 is the correct answer
Country of Resident – INDIA
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 is
1. SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Because ‘_[_]% ‘ represents
_ —> Represents first character of the word that may be any character
[_] —> ‘_’ character in ‘['']‘ square braces represents looking for this
character the square brace will nullify(ignores) its(special character)
meaning.
% —> any number of character after [_]
if u want display only second letter is ‘_’ then the option 1 is correct
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]%’
if we want disply second letter as well as any where in the name ‘_’ is exists
option 3rd is coorect
SELECT * FROM [Grant]
WHERE GrantName like ‘_%[_]%_’
country : India
Correct Answer is Option 1
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Country – INDIA (Gujarat)
if u want display only second letter is ‘_’ then the option 1 is correct
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]%’
country : India
Correct Answe is 3
The options 1 and 3 are giving same result. But my vote is for Option-3
Narendra (India)
Correct answer is option 1 :
First _ will work as wildcard character and will result all the GrantNames.
Second _ is in Square Brackets and will result all the GrantNames having _ as Second character.
Country – India
my answer is Option 1, i.e SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
I’m from INDIA
#1 is the correct answer to find all rows with GrantName having an underscore as the second character.
Jason from USA
Option 1
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
is correct.
country: India
Correct answer is:
1.SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Country ==> United States
1. Is the correct answer. The _ wildcard in the first position in the phrase after quotation ask SQL to litterally look for the second letter. The _ in square brackets ask SQL to sort by that symbol.
Ron A. Farris
Country – United States of America
First,
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Country Canada
The answer is 1.
SELECT * FROM [Grant] WHERE GrantName LIKE ‘_[_]%’
Country = USA
Correct answer :-
Option 1
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
grant names that have and Underscore as the second letter find by
‘_[_]%’ pattern with like .. as second letter is Underscore which is wildcard
so , put it into [ ] ..
country: India
Nice post …
The correct answer is option 1:
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]%
Reason: _ being a wildcard character, provides appropriate output required.
Divya
US
SELECT * FROM [Grant] WHERE GrantName like ‘_[_]% ‘
-
Rahul
Hyderabad (India)
Correct answer is #1 SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Thank you
Lisa (USA)
SELECT * FROM [Grant] WHERE GrantName like ‘_[_]% ‘
Question #1 is correct.
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
–
Timmy
TX, USA
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
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 ‘_[_]% ‘
Number 1 is the closest, but there is an empty space char at the end of each of the string literals, so that would cause ALL to be invalid.
Here is the correct version:
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]%‘
USA
Correct one is —> SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Vaibhav Mathur
India
The correct answer is #1 – all other answers are obviously incorrect.
I’m from USA
Answer: 1. SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Residence: USA
1.SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Option 1. Regexp for the win, even if SQL Server’s is a bit half-assed. Texas, USA. Whee!
Answer: Option #1
Explanation: The first underscore ‘_’ is treated as a single character wildcard. The second underscore in square brackets [_] is treated as a literal character to search for. Option #4 is not correct because it uses an ‘=’ instead of LIKE.
Country: USA
Correnct Answer is : Option 1
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Thanks
Dhruval Shah, Ahmedabad, India.
Answer :Option1
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Thanks
Riyas.V.K
Correct Answer is Option 1:
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
India
Correct answer is Option 1
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]%
Correct answer is
Option 1: SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Thanks
Rupesh Tiwary(India)
Thank you pinal sir,
Really Glad about results.
Thank you very much.
Hi dave,
Correct answer is option 1 :
SELECT * FROM [Grant] WHERE GrantName like ‘_[_]% ‘
because the first underscore is acts as a wildcard character which signifies a single character and matches Underscore as the second letter
From
India
Chitti :-)
Dear Pinal sir,
Answer is SELECT * FROM [Grant] WHERE GrantName like ‘_[_]% ‘
Answer #1
1. SELECT * FROM [Grant] WHERE GrantName like ‘_[_]% ‘
Jason from the USA
Correct answer is option 1 :
SELECT * FROM [Grant] WHERE GrantName like ‘_[_]% ‘
Reason: underscore is a wildcard character which signifies a single character and matches Underscore as the second letter
Country – India
The correct answer is #1.
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
As we are required to search “_” as second character, specifying and “_” before and second “_” in brackets with wild card will give you all the GrantName that has second character as “_”.
Syed from USA.
option 1 is the answer
USA RA
# 1 is the answer.
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.
Q 2) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Wildcard – Querying Special Characters – Day 2 of 35
Answer: 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.
Winner from USA: Chuck Lathrope
Winner from India: Chirag Satasiya
I thank you all for participating here. The permanent record of this update is posted on facebook page.
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
Pingback: SQL SERVER – The SQL Hands-On Guide for Beginners – Book Available for SQL Server Certification Journey to SQLAuthority
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
The correct choice is 1
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Option 1,
I am from India
Answer is #1:
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Thanks,
Wayne
answer is # 1
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
this query will give the 2 letter to be conatin ‘_’ first will be any character
Answer:Option 1
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
to find Second character we use _ before [_]%,since _ is a wildcard we are using it inside [].
Anjana (India)
Correct answer : Option 1
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
I am From USA
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Option 1 is correct
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Correct answer is 1
1.SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Correct Option is 1.
SELECT * FROM [Grant] WHERE GrantName like ‘_[_]% ‘
Explanation: In Like _ is used for single character and to search _ in character list we use square brackets.
India [Noida]
hi,
option 1
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
The Correct answer is 1#
1.SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
ShashiKanth Pusa
United States
#1. SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Sreejith Vijayan
India
Ans: 1 SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
india,Bangalore
The correct answer is #1
SELECT * FROM [Grant]
WHERE GrantName line ‘_[_]%’
Manila, Philippines
This is a correction to my post comment
Answer:
SELECT * FROM [Grant]
WHERE GrantName LIKE ‘_[_]%’
Option 1 is the correct answer.
First option is the coreect answer
Option 1: SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Country – India
Correct Answer is : 1) SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Country : India
Pingback: SQL SERVER – Query Hint – Contest Win Joes 2 Pros Combo (USD 198) – Day 1 of 5 « SQL Server Journey with SQL Authority
SELECT * FROM [Grant]
WHERE GrantName like ‘_[_]% ‘
Answer is option 1