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
Correct answer is option 5
Varinder Sandhu (India)
my answer is Option 5
SELECT * FROM Employee
WHERE Firstname like ‘%”%‘
I’m from india
Hi Pinal sir,
The correct answer for this question is option no. 5:
SELECT * FROM Employee WHERE Firstname like ‘%”% ‘
Reason:
We are finding first names that have an apostrophe anywhere in the name so apostrophe may be at beginning or between characters or at the end of string.
Above pattern is correctly formed with % at the beginning and end of string to find apostrophe anywhere in the name and for apostrophe we have used two single character to remove its special meaning with correct keyword i.e. “Like”.
Following options are not correct because:
Option 1: SELECT * FROM Employee WHERE Firstname like ‘_’% ‘
incorrect pattern and missing of additional single quote.
Option 2: SELECT * FROM Employee WHERE Firstname like ‘_”% ‘
Underscore is used instead of %.
Option 3: SELECT * FROM Employee WHERE Firstname like ‘_[‘]% ‘
Incorrect pattern with character class.
Option 4: SELECT * FROM Employee WHERE Firstname like ‘%’% ‘
Incorrect pattern.
Option 6: SELECT * FROM Employee WHERE Firstname like ‘%[‘]% ‘
Incorrect pattern with character class.
Thanks
Chirag Satasiya (Mumbai – INDIA)
Option 5:
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Because single quote escaped using two single quotes.
Country : India
Answer :
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
1) SELECT * FROM Employee
WHERE Firstname like ‘_’% ‘
This query will give error for unclosed quotation as we have not provided ending quotation
2) SELECT * FROM Employee
WHERE Firstname like ‘_”% ‘
This query will look for first char should be n e thing and the second char will be the apostrophe. so tits not right query as per the req.
3) SELECT * FROM Employee
WHERE Firstname like ‘_[‘]% ‘
Again because lack of one quotation this query will produce error of incorrect syntax.
4) SELECT * FROM Employee
WHERE Firstname like ‘%’% ‘
Again because lack of one quotation this query will produce error of incorrect syntax.
5) SELECT * FROM Employee
WHERE Firstname like ‘%[‘]%
Again because lack of one quotation this query will produce error of incorrect syntax.
Please correct me if am wrong any where
Thanks & Regards,
Jigar badgujar
Answer is :
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Country: India
Answer : 5
City: Mumbai
Country: India
The correct option is 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Using single quote will result in unclosed quotation mark error.
so we have to use two single quote.
% wild character finds zero or any number of characters which includes apostrophe .
Yasodha.N( India)
Option 5:
SELECT * FROM Employee
WHERE FirstName LIKE ‘%”%’
Sudeepta, India
Option 5 – is correct option.
Option 1 – will generate error.
Option 2 – This code will find out only 2nd char as ‘ . So this is wrong.
Option 3 – [ ] will not work with ‘.
Option 4 – this code will result in to error.
Option 6 – again [] will not work in this case.
I am from India.
Thanks and regards
-Dnyanesh
Answer is:
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Option 5 is answer.
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Correct answer is No. 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Country : India
Correct answer is option 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Thanks,
Kannappan
Bangalore
Correct answer is option 5
SELECT * FROM Employee
WHERE Firstname like ‘%”%‘
Explanation : As we know single quotation mark is treated by MS-SQL as either start or end of string. We have to use two quotation mark for having a single quote in string. And the wildcard character ‘%’ represents any number of characters. Hence the expression ‘%”%‘ matches with all those expression having single quote at any of the position in string.
Country – INDIA (Gujarat)
Correct option is 5
Option 1 & 4 will end up with “Unclosed quotation mark ” error
Option 3 & 6 will end up with “Incorrect syntax near ‘]’” error
Option 2 – will yield Employee records who’s FristName’s second character is *Apostrophe*, which not our requirement.
Ritesh
India
From India:
The answer will 5th. ie.,
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Answer is 5
Because, ‘%”%’ will give the desired result
To find all the names having an apostophe will require two ” for sql server to understand and making two % signs on both side will result all the names having an apostrophe in any position..
Hence, answer 5 is correct.
Malay Shah,
Ahmedabad,
India
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Option 5 is answer.