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 http://blog.sqlauthority.com which is next day GTM+2.5.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Correct answer is option 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Thanks
Dhananjay Kumar
Answer is -5
5.SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Neelesh Jain
India
Correcto answer is No. 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Correct answer is No. 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Rene Castro (El Salvador)
answer is 5
using two single quotes in the string let sql server recognise it as single quote.
Australia
Answer is option 5:
5.SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
because option 5 has double apostrophes to look for string literal
Country – India
Thanks.
Correct Answer: 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Thanks,
Bindish Thakkar
USA
5.
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
% is for any (or no) characters
‘ is the escape sequence for ‘
Leo Pius
USA
Answer is Option 5.
This query will return employees with apostrophe anywhere in the firstname.
Why other options are wrong?
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.
Sandeep
INDIA
Answer is 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Rahul
Hyderabad
Correct Answer is option 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Thanks
Prasad Yangamuni
India (Pune)
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 is 5
5. SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Because As mentioned in earlier posts Wild cards works only with LIKE keyword and Now let us look at the where clause condition ‘%”%’
Which represents as follows
% —–> Represents Zero or more characters
” —–> To use Single quote as filter character, two continuous single quotes
should be mentioned so that to nullify its meaning
% character is specified before and after the two single quotes, Because we are looking for the single quote anywhere in the FirstName
Thanks for the Post :-)
Country: India
Option 5 would work fine.
SELECT * FROM Employee
WHERE Firstname like ‘%”%’
– after like there is one single quote, than percentage sign, after that TWO SINGLE QUOTE (not one double quote) than again percentage and single quote.
Ritesh Shah (India)
Correct answer is option 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
we are finding apostrophe anywhere in the string, that’s why we put another apostrophe precede the main one.
India
Answer is
5.SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Country :India [Ahmedabad]
Option 5 :
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
is the correct answer.
India
Correct Answer is : 5
5. SELECT * FROM Employee WHERE Firstname Like ‘%”%’
Gopalakrishnan Arthanarisamy
India
To find all the names that have an apostrophe anywhere in the firstname from the employee table, we should use LIKE and option 5 is the correct answer.
SELECT * FROM Employee
WHERE FirstName like ‘%”%’
From USA.
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Aditya
Chennai, INDIA
Correct Answer is: Option 5 :
SELECT * FROM Employee
WHERE Firstname like ‘%”%’
Explanation: We can find all first names that have an apostrophe anywhere in the name by using two single quotes, it filters our result set for all the first name with an apostrophe.
Country: INDIA[Noida]
Thanks,
Dips
Option 5 is correct.
SELECT * FROM Employee WHERE FIRSTNAME LIKE ‘%”%’
INDIA
Correct Answer:
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Pratik Raval
India
The correct answer is option 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Here we want to find all first names that have an apostrophe anywhere in the name so we have to put % at start and end so character anywhere in string can be found to find apostrophe we need two single quotes (”) in between % sign.
I am From
INDIA
I will go with option 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Explanation – wildcard character ” = ‘
If you are wishing to use “abhimanyu’s” then use “abhimanyu”s” and this will treated as “abhimanyu’s”.
Country: India (Bokaro Steel City)
Question #5 is correct.
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Timmy
TX, USA
Right answer is option 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Explanation :
For finding all first names that have an apostrophe anywhere in the name
you have to used this % sign followed by apostrophe again followed by apostrophe. This will search all names like all combinations of Alphabets having apostrophe anywhere in string.
Thanks & Regards
Sunny B. Jagtap
From INDIA [Pune]
I think it is answer 5 you explained it in post itself
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Answer is option 5
Option-5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
The Correct Answer is 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Narendra (India)
As you have already mentioned in the post,
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.
Ishan Shah,
Gandhinagar,
India
correct answer option 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Thanks
Kirti Darji
Correct Option is 5
means
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
because we have to prefix the single quote by another single quote to suppress its special meaning
Thanks,
Santosh
India
Correct answer is :
5.SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Mandar,
Mumbai, INDIA
Option 5 is correct one:
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Thanks
Vivek Srivastava
5. SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
sql server takes two single quotes as a single so we have to give two singe quotes.
ghanshyam
bangalore
Correct option is 5
Genarally ‘%%’ is use to match any word or character at any position and %”% will match single quote at any position either first or last or middle.
karan
India
OPTION 5 is Correct answer
Country : India
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
[...] Q 3) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Finding Apostrophes in String an… [...]
the correct option is option 5 ie
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
because using two single quote will help you to find string with apostrophe anywhere in string..
i am from india
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.
This correct option is 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Thanks
Vijayakumar .P [Kochi] India
From India:
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Option 5 is answer.
The answer will 5th. ie.,
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
From India
Correct Answer is – 5
You will get no record if you run 2. Also you will get the syntax error for the rest because quote are not in the right usage.
Country – UK
Correct answer is
5. SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
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.
Bhargav Mistri, INDIA
Correct Answer is option 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Hiren Bavishi
India
Correct answer is 5.
None of the above is correct:
Many of them have replied as option 5 but what i see is , it is a double quote(“) not a single quote.
The correct answer is : SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
The reason for this is :
Two single quotes in a row signify an escape sequence from the normal interpretation of the single quote character. When two single quotes appear together, they are interpreted by SQL as one literal single quote.
Santosh.s
Bangalore – India
”
I typed single quotes two times .
This is HTML rendering problem I think
Correct Answer is Option 5 :
SELECT * FROM Employee WHERE Firstname like ‘%”% ‘
Ahmedabad, INDIA
oops!!! I am sorry.. i mistaked it to double quotes.
option 5 is correct.
The reason for this is :
Two single quotes in a row signify an escape sequence from the normal interpretation of the single quote character. When two single quotes appear together, they are interpreted by SQL as one literal single quote.
Santosh.s
Bangalore – India
The answer is option 5 :
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
we need to use single quote twice if we want to use as a filter
Again the answer is 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Hi,
option -5 is correct
Correct answer is
5. SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Country : India
Madhivanan
Correct Answer is Option 5 :
SELECT * FROM Employee WHERE Firstname like ‘%”% ‘
The Correct option is 6. SELECT * FROM Employee
WHERE Firstname like ‘%[']% ‘
Because since apostrophe is a reserve key letter in SQL you would have to use escape character ['] to let SQL recognize it as literal and since it could be anywhere in the firstname then the correct option is 6.
[I am from Nigeria.]
Answer is option 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Country: India
Correct answer is option 5.
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Country : India
Shilpa Sharma
Answer is option 5
Ritesh
vadodara India
Answer 5 is correct as we need to find out anywhere in the name
Correct Answer – 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Thanks…
Rajneesh Verma
INDIA
The answer is #5
USA
Mike Michalicek
Correct Answer is 5,
Country: UK
City: Oxford
Correct answer is
5. SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Country : India
Satish Singh
Correct Answer is:
Option 5 :
SELECT * FROM Employee
WHERE Firstname like ‘%”%’
option 1,2,3 are directly negated because of wild character
option 4 will generate error because the query results in undisclosed quotation mark
option 5 : this is the right one
the first ‘ after % works as a ‘escape’ for the following ‘
option 6 : it will generate syntex error as query is suppose to end when second apsotrophe encounters.
country : india
Option 5.
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Is correct answer.
I am from India
Correct Answer: 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Vishal (India)
Correct answer is option 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Correct Answer: Option 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
-Aashish Thakker (USA)
SELECT * FROM Employee
WHERE Firstname like ‘%”%’
Chetan (USA)
Option 5 is correct answer – As per the above theory 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.
If the character string contains an embedded single quotation mark, you should insert an additional single quotation mark in front of the embedded mark
Saravanakumar. S
Country – India
Answer: Option 5
SELECT * FROM Employee
WHERE Firstname like ‘%”%‘
Correct Answer is: Option 5 :
SELECT * FROM Employee
WHERE Firstname like ‘%”%’
Correct Answer is: Option 5 :
SELECT * FROM Employee
WHERE Firstname like ‘%”%’
Charlotte NC,USA
Correct answer is 5 SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Lisa
USA
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
MOHAMMED ZUHEB
INDIA
5th is the Answer
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
its similar like insert a Firstname with an apostrophe (single quote)
insert into Employee (Firstname) values (‘Micheal”s’)
Answer: Option 5
Because here the predicate starts and ends with % which means that there could be any character before and after apostrophes and there are two apostrophes which means that the 1st apostrophes is negating the next one and telling SQL Server to treat 2nd apostrophes as a part of string.
Country:India
Hi Pinal,
Challenge:
To find all first names that have an apostrophe anywhere in the name.
Solution:
To find all first names that have an apostrophe anywhere in the name, use the following query (5th choice of the six listed):
5.SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Explanation:
The Percent Wildcard (%) at the beginning gives all rows where name has zero or more characters before encountering the first apostrophe.
The two single apostrophes together says search for an apostrophe in the name.
The first apostrophe is signalling SQL Server to look for the next character explicitly in the string.
The second apostrophe is the character to look for.
The Percent Wildcard (%) at the end gives all rows where name has zero or more characters after encountering the apostrophe.
Country of residence:
United States
Thanks for the knowledge!
Regards,
Bill Pepping
Option 5 :)
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
USA
At first glance I would agree with Ritesh on the answer (option 5) and the explanation for not selecting the other options.
However, there seem to be an extra space in the option 5 after the second wildcard:
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
I presume it is supposed to be:
SELECT * FROM Employee
WHERE Firstname like ‘%”%’
As explained in the link from MSDN http://msdn.microsoft.com/en-us/library/ms189454.aspx
Wildcard – Matches any string of zero or more characters. This wildcard character can be used as either a prefix or a suffix.
A single quote would return the unclosed quotation mark error therefore you require double quotes.
Thanks,
Shivani (USA)
5 is the correct answer. You have to escape the first apostrophe by doubling them up.
Eric (USA)
Answer = 5
5. SELECT * FROM Employee WHERE Firstname like ‘%”% ‘
Country = USA
Correct Answer 5 : SELECT * FROM Employee WHERE Firstname like ‘%”% ‘
Option 5 is the correct answers
SELECT * FROM Employee
WHERE Firstname like ‘%”%‘
This will search the Firstname column which has atleast 1 apostrophe. Other queries will error out and not return any results
Option 5 is closest, but there again, you have a blank char at the end of each item which would cause an issue for all of them.
Corrected option 5:
SELECT * FROM Employee
WHERE Firstname like ‘%”%‘
Option 5 is closest, but there again, you have a blank char at the end of each item which would cause an issue for all of them.
Corrected option 5:
SELECT * FROM Employee
WHERE Firstname like ‘%”%‘
I forgot the country again! USA
The correct anser is :
5.SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Country: United States
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
#5 SELECT * FROM Employee WHERE Firstname like ‘%”%’
You need the”%” at the start and end so that any number of characters can be before or after the apostrophe, while the two single quotes between the “%” signs indicate that the desired character is an apostrophe.
USA
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Answer is 5
SELECT * FROM Employee
WHERE LastName like ‘%”%’
Thanks
Shree
Option 5 would work fine.
SELECT * FROM Employee
WHERE Firstname like ‘%”%’
Dhanu Dhanapalan(India)
Correct Answer is Option 5 :
SELECT * FROM Employee
WHERE Firstname like ‘%”%’
5.SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Vinay,Pune
Option 5 is the correct answer.
5. SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
All the other queries will return error messages
SOumya Ranjan Das
India
When I ran #2, it did find apostrophes, however, it did not catch all of the apostrophes. #5 caught all of the apostrophes, but, does have an extra space on the end.
the ans is 6
5. SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘;
Because this record has the single quote in (firstname) result set.
INDIA (Bangalore)
Correct answer : option 5
Option : 1, 3, 4 and 6 -> Syntax error
Option : 2 -> It will fetch the name which have Apostrophe exactly in the second position.
Option : 5-> It will fetch the first names that have an apostrophe anywhere in the name
Country : USA (Chicago)
Should be –> SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Vaibhav Mathur
India
Option 5
SELECT * FROM Employee
WHERE Firstname like ‘%”%‘
Hi Pinal Sir,
The correct answer for the above question is Option No. 5.
SELECT * FROM Employee WHERE Firstname like ‘%”%‘
Explanation
We need to find Persons name that have an apostrophe anywhere in the name ,so it can be anywhere from starting till the end anywhere.
1) In above option LIke operator suggests that we have some criteria and what all satisfy will be selected.
2) % operator : it can be 0 to many characters from start till end.
3) ” two single quotes tells the sql server that we need at least one apostrophe in the name.
4) % operator : it can be 0 to many characters from start till end.
NOTE 1) Make Sure that there is no space anywhere in the searching formula otherwise sql will look for apostrophe and space specified before/ after as specified then the answer or the result set will be surprising.
The remaining options are in correct because:
Option 1: SELECT * FROM Employee WHERE Firstname like ‘_’% ‘
Unclosed quotation mark after the character string,Incorrect syntax near ‘ .
Option 2: SELECT * FROM Employee WHERE Firstname like ‘_”% ‘
Underscore is used instead of %, No results are returned wrong Query.
Option 3: SELECT * FROM Employee WHERE Firstname like ‘_[']% ‘
Incorrect syntax near ‘]’, Incorrect pattern we are searching the apostrophe anywhere in the string not only from second and also syntax error.
Option 4: SELECT * FROM Employee WHERE Firstname like ‘%’% ‘
Unclosed quotation mark after the character string, error raised Wrong Option.
Option 6: SELECT * FROM Employee WHERE Firstname like ‘%[']% ‘
Incorrect syntax near ‘]’, Query not formed properly.
Hope to see more challenging one in next post, this is a good initiative taken by Pinal sir where in all are learning something new and useful I am happy that I am a part of this.
Dilip Kumar Jena – INDIA
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
kkmishra (jss academy of technical education noida)
India
Correct answer is option 5
SELECT * FROM Employee
WHERE Firstname like ‘%”%
Manoj ,New Delhi
Answer: Option 5
SELECT * FROM Employee
WHERE Firstname like ‘%”%‘
SQL Server sees everything after that second single quote as an error in SQL code. To forego the special meaning of the single quote, precede it with another single quote.
USA
SELECT * FROM EMPLOYEE
WHERE Firstname like ‘%”%’
The correct answer is option 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Nikhil from Tampa, FL, USA
answer i s option 5
Good Post,
sandhya
Boston,usa
The correct answer is #5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
I’m from USA
Answer 5: SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Residence: USA
Correct Answer: 5.
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Redmond, WA [USA]
Number 5 would be the correct answer.
Jason
USA
5.SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Yup, Number 5, I’ve had to do that enough with Dynamic SQL. Texas, USA.
Correct Answer is : Option 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Thanks,
Dhruval Shah, Ahmadabad, India.
Answer : Option 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Thanks
Riyas.V.K
Option 5 Is correct
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Correct Answer is Option 5:
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
India
Correct answer is option 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Thanks
Rupesh Tiwary(India)
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
The correct answer is option 5
5.SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
The correct answer is option 5
5.SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Hemant (India)
Answer: Option 5
SELECT * FROM Employee
WHERE Firstname like ‘%”%’
the correct answer is
Option 5:
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Country – India
Q 3) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Finding Apostrophes in String and Text – Day 3 of 35
When pattern matching the _ forces on letter and the % can be any number of letters (include zero letters). Since a name could start with an apostrophe we would not find just those with the underscore first so (1), (2), and (3) are all wrong. Both (4) and (6) would be delimited by the special meaning of the second apostrophe. The use of LIKE ‘%”% ‘ will treat not use the special meaning and have a % on either side so (5) is correct.
Winner from USA: Cameron
Winner from India: Madhivanan
I thank you all for participating here. The permanent record of this update is posted on facebook page.
[...] Q 3) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Finding Apostrophes in String an… [...]
SELECT *
FROM Employee
WHERE Firstname like ‘%['']%’
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
The answer is #5:
SELECT * FROM Employee WHERE Firstname like ‘%”% ‘
Francisco
Miami
Correct answer is :
SELECT * FROM Employee WHERE Firstname like ‘%”%’
Correct answer is option 5
SELECT * FROM Employee
WHERE Firstname like ‘%”%
My Country is Canada
The correct choice is 5
From Canada
Correct Answer is 5.
Kapil Dev Tripathi
(Noida, India)
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Option 5
I am from India
Answer is #5
SELECT * FROM Employee
WHERE Firstname like ‘%”%
Thanks,
Wayne
answer is # 5
SELECT * FROM Employee
WHERE Firstname like ‘%”%
i’m atul singh
and i am from india(mumbai)
Answer: Option 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Anjana(India)
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Option 5 is correct
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
Correct Answer is 5
SELECT * FROM Employee
WHERE Firstname like ‘%”%
Correct Answer is 5
SELECT * FROM Employee WHERE Firstname like ‘%”%
Explanation: Using two single quotes filters result set with an apostrophe
India
The correct answer for this question is option no. 5:
SELECT * FROM Employee WHERE Firstname like ‘%”% ‘
Sreejith Vijayan
India
Ans:5 SELECT * FROM Employee WHERE Firstname like ‘%”%’
india,bangalore
Correct answer is option 5
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
INDIA
Ans : 5 ) SELECT * FROM Employee WHERE Firstname like ‘%”% ‘
Mohamed Asif
Ans : 5 ) SELECT * FROM Employee WHERE Firstname like ‘%”% ‘
Country : India
[...] SQL Joes 2 Pros Development Series – Finding Apostrophes in String and Text [...]
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘
SELECT * FROM Employee
WHERE Firstname like ‘%”% ‘