SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Finding Apostrophes in String and Text – Day 3 of 35

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?

  1. SELECT * FROM Employee
    WHERE Firstname like ‘_’% ‘
  2. SELECT * FROM Employee
    WHERE Firstname like ‘_”% ‘
  3. SELECT * FROM Employee
    WHERE Firstname like ‘_[']% ‘
  4. SELECT * FROM Employee
    WHERE Firstname like ‘%’% ‘
  5. SELECT * FROM Employee
    WHERE Firstname like ‘%”% ‘
  6. 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)

About these ads

194 thoughts on “SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Finding Apostrophes in String and Text – Day 3 of 35

  1. Correct answer is No. 5

    SELECT * FROM Employee
    WHERE Firstname like ‘%”% ‘

    Rene Castro (El Salvador)

  2. 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.

  3. 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

  4. 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

  5. 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)

  6. 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

  7. 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.

  8. 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

  9. 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

  10. 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)

  11. 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]

  12. 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

  13. 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

  14. 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

  15. 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

  16. Pingback: SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

  17. 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

  18. 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)

  19. Option 5:
    SELECT * FROM Employee
    WHERE Firstname like ‘%”% ‘

    Because single quote escaped using two single quotes.

    Country : India

  20. 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

  21. 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)

  22. 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

  23. 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)

  24. 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

  25. 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

  26. This correct option is 5

    SELECT * FROM Employee
    WHERE Firstname like ‘%”% ‘

    Thanks
    Vijayakumar .P [Kochi] India

  27. 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

  28. 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

  29. 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

  30. 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

  31. 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.]

  32. Correct answer is option 5.

    SELECT * FROM Employee
    WHERE Firstname like ‘%”% ‘

    Country : India

    Shilpa Sharma

  33. 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

  34. 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

  35. 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’)

  36. 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

  37. 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

  38. 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)

  39. 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

  40. 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 ‘%”%‘

  41. 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

  42. #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

  43. 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.

  44. 5. SELECT * FROM Employee
    WHERE Firstname like ‘%”% ‘;

    Because this record has the single quote in (firstname) result set.
    INDIA (Bangalore)

  45. 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)

  46. 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

  47. 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

  48. The correct answer is option 5
    SELECT * FROM Employee
    WHERE Firstname like ‘%”% ‘

    Nikhil from Tampa, FL, USA

  49. 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

  50. 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

  51. 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.

  52. 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.

  53. 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

  54. The correct answer is 5.

    sql considers single quote a delimiter for string. to escape single quote, we can use double quote.

    Country – Canada

  55. 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

  56. 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.

  57. Pingback: SQL SERVER – The SQL Hands-On Guide for Beginners – Book Available for SQL Server Certification Journey to SQLAuthority

  58. Correct Answer is 5

    SELECT * FROM Employee WHERE Firstname like ‘%”%

    Explanation: Using two single quotes filters result set with an apostrophe

    India

  59. Pingback: SQL SERVER – Query Hint – Contest Win Joes 2 Pros Combo (USD 198) – Day 1 of 5 « SQL Server Journey with SQL Authority

  60. Pingback: SQL SERVER – Weekly Series – Memory Lane – #040 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s