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.
Finding Apostrophes in string and text
For the last two days we have been using wildcard examples from the Beginning SQL Joes 2 Pros Volume 1 book. Today is our last wildcard example. Please take one more look all the records in the Grant table of the JProCo database. Notice GrantID 004 and 005 have a single quote (apostrophe) in the name. See figure below:
What if you want to find grants that have an apostrophe (single quote) in their names such as Norman’s Outreach? Everything inside single quotes after the LIKE evaluates every record to give you your final result set.
The first single quote starts the string and it ends with the second single quote. Everything between the single quotes is part of the search string. Everything before the first single quote and after the second single quote is not part of the search string. The single quote encompasses or delimits the pattern you are searching. A new challenge arises here. The following query produces a syntax error.
--Bad query results in an error. SELECT * FROM [GRANT] WHERE GrantName LIKE '%'%'
'Msg 105, Level 15, State 1, Line 4
Unclosed quotation mark after the character string ‘
The problem lies in the fact that SQL Server assumes the predicate is done after the second single quote. SQL Server sees everything after that second single quote as an error in your SQL code. Your intentions were lost or misunderstood. To forego the special meaning of the single quote, precede it with another single quote. The code and results are seen in the figure below.
Using two single quotes filters your result set for a grant name with an apostrophe. You now have two records with a single quote in your result set. To view all names without a single quote you would simply change the LIKE to NOT LIKE in the WHERE clause.
--Find GrantNames without a single quote SELECT * FROM [GRANT] WHERE GrantName NOT LIKE '%''%'
Note: If you want to setup the sample JProCo database on your system you can watch this video.
Question 3:
Q 3:You want to find all first names that have an apostrophe anywhere in the name. Which SQL code would you use?
- SELECT * FROM Employee
WHERE Firstname like ‘_’% ‘ - SELECT * FROM Employee
WHERE Firstname like ‘_”% ‘ - SELECT * FROM Employee
WHERE Firstname like ‘_[‘]% ‘ - SELECT * FROM Employee
WHERE Firstname like ‘%’% ‘ - SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘ - SELECT * FROM Employee
WHERE Firstname like ‘%[‘]% ‘
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 which is next day GTM+2.5.
Reference: Pinal Dave (https://blog.sqlauthority.com)
191 Comments. Leave new
Answer: Option #5
Explanation: It has two single quotes in the string where the first one acts as the ESCAPE character. The wildcard % on both sides looks for the quote anywhere in the string.
Country: USA
Option 1:
SELECT ‘_’%’
Query output:
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ”.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ”.
Option 2:
SELECT ‘_”%’
Query output:
_’% this will fetch only records which have _’.so we can’t use this option
Option 3:
SELECT’_[‘]%’
Query output:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘]’.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ”.
Option 4:
SELECT’%’%’
Query out put:
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ”.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ”.
Option 4:
SELECT’%”%’
Query output:
%’%
The % on both sides looks for the Apostrophes anywhere in the string. We can use this ‘%”%’ option to find Apostrophes
Option 5:
SELECT’%[‘]%’
Query output:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘]’.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ”.
Answer: Option #5
India
wrongly updated answer
Answer is option #4
inida
SELECT * FROM Employee WHERE Firstname Like ‘%”%’
I am from India
mine 5 too
Dear Pinal Sir,
Answer is SELECT * FROM Employee WHERE Firstname like ‘%”% ‘
Regards
Answer:
Option 5 : SELECT * FROM Employee WHERE Firstname Like ‘%”%’
Hello. Really enjoying the series.
The closest answer would be 5, since I believe that is meant to be two single quotes. Although either your blog or my browser converted the two single quotes into one double quote, which would not work.
Anyway, the reason is that you need two single quotes in order to represent a literal single quote, otherwise it will end your string and error. The wildcards before and after then allow that single quote to be anywhere in the string and with anything else around it.
I am from the United States.
Correct answer is No. 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
I am from India.
Correct answer is #5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
As explained in the article will get you all the firstname with ” ‘ ” anywhere in the firstname.
Syed from USA.
option 5 is the correct answer
USA RA
Correct answer is No. 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
USA
Correct answer is No. 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
I am from Ahmedabad,Gujarat,India.
The correct answer is Option 5:
SELECT * FROM Employee
WHERE Firstname like ‘%”%’
This uses the wildcard value at the beginning and end of the string. To forego the special meaning of the single quote, we precede it with a second single quote, and therefore will find any string containing a single quote.
Country of Residence: USA
answer is #5
from USA
The correct answer is 5.
sql considers single quote a delimiter for string. to escape single quote, we can use double quote.
Country – Canada
The correct answer is 5
Country – India
Option : 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
its a correct Answer
My Country : India
SELECT * FROM Employee WHERE Firstname Like ‘%”%’
gives Correct Ans.
From-India
The correct answer is Option 5:
SELECT * FROM Employee
WHERE Firstname like ‘%”%’
Anil (India)
Question 3:
Q 3:You want to find all first names that have an apostrophe anywhere in the name. Which SQL code would you use?
Answer:
5.SELECT * FROM Employee
WHERE Firstname like ‘%”%
Answerer: Sivaprasad S
Country: India