Finding a quote in a string

  • What can I use to find if a string contains a '? I know how to replace the quote with a blank but I don't know how to determine if the string contains a quote.

    --If @dest_city contains a quote

    select @dest_city = replace(@dest_city,'''',' ')

  • string_column LIKE '%''%'

    or:

    CHARINDEX('''', string_column) > 0

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Thank you Scott.

  • Since quote is also CHAR(25), you can also do this:

    CHARINDEX(CHAR(25), string_column) > 0


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (12/19/2012)


    Since quote is also CHAR(25), you can also do this:

    CHARINDEX(CHAR(25), string_column) > 0

    Single Quote is char(39) Dwain......if I am not wrong. 😉

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • Another way:

    where len(Col) > len(replace(Col, '''', ''))

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • vinu512 (12/20/2012)


    dwain.c (12/19/2012)


    Since quote is also CHAR(25), you can also do this:

    CHARINDEX(CHAR(25), string_column) > 0

    Single Quote is char(39) Dwain......if I am not wrong. 😉

    Looked like a single quote when I SELECTed it but then sometimes my eyes aren't so good.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (12/20/2012)


    vinu512 (12/20/2012)


    dwain.c (12/19/2012)


    Since quote is also CHAR(25), you can also do this:

    CHARINDEX(CHAR(25), string_column) > 0

    Single Quote is char(39) Dwain......if I am not wrong. 😉

    Looked like a single quote when I SELECTed it but then sometimes my eyes aren't so good.

    Don't worry Dwain......I'm always there to help 😀

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • dwain.c (12/20/2012)


    vinu512 (12/20/2012)


    dwain.c (12/19/2012)


    Since quote is also CHAR(25), you can also do this:

    CHARINDEX(CHAR(25), string_column) > 0

    Single Quote is char(39) Dwain......if I am not wrong. 😉

    Looked like a single quote when I SELECTed it but then sometimes my eyes aren't so good.

    I can't display CHAR(25) on any of my servers, so it must be foreign 😛

    SELECT N, CHAR(N) AS CharResult

    FROM (SELECT TOP 255

    ROW_NUMBER() OVER (ORDER BY(SELECT NULL))

    FROM master.sys.all_columns sc1

    CROSS JOIN master.sys.all_columns sc2

    )a(N);

    Or, narrowed down to find the single quote: -

    SELECT N, CHAR(N) AS CharResult

    FROM (SELECT TOP 255

    ROW_NUMBER() OVER (ORDER BY(SELECT NULL))

    FROM master.sys.all_columns sc1

    CROSS JOIN master.sys.all_columns sc2

    )a(N)

    WHERE CHAR(N) = '''';

    Yep, CHAR(39). I used it far more than I should due to dynamic SQL :w00t:


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • vinu512 (12/20/2012)


    dwain.c (12/20/2012)


    vinu512 (12/20/2012)


    dwain.c (12/19/2012)


    Since quote is also CHAR(25), you can also do this:

    CHARINDEX(CHAR(25), string_column) > 0

    Single Quote is char(39) Dwain......if I am not wrong. 😉

    Looked like a single quote when I SELECTed it but then sometimes my eyes aren't so good.

    Don't worry Dwain......I'm always there to help 😀

    The weird thing about this is that now when I SELECT CHAR(25) it doesn't look at all like a quote. So I'm not sure what I selected when I did it.

    Must be too much holiday cheer. :crazy:


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (12/24/2012)


    vinu512 (12/20/2012)


    dwain.c (12/20/2012)


    vinu512 (12/20/2012)


    dwain.c (12/19/2012)


    Since quote is also CHAR(25), you can also do this:

    CHARINDEX(CHAR(25), string_column) > 0

    Single Quote is char(39) Dwain......if I am not wrong. 😉

    Looked like a single quote when I SELECTed it but then sometimes my eyes aren't so good.

    Don't worry Dwain......I'm always there to help 😀

    The weird thing about this is that now when I SELECT CHAR(25) it doesn't look at all like a quote. So I'm not sure what I selected when I did it.

    Must be too much holiday cheer. :crazy:

    Or is it too much holiday boozing dwain??....I suspect the latter.......:-D:-P

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • vinu512 (12/24/2012)


    dwain.c (12/24/2012)


    vinu512 (12/20/2012)


    dwain.c (12/20/2012)


    vinu512 (12/20/2012)


    dwain.c (12/19/2012)


    Since quote is also CHAR(25), you can also do this:

    CHARINDEX(CHAR(25), string_column) > 0

    Single Quote is char(39) Dwain......if I am not wrong. 😉

    Looked like a single quote when I SELECTed it but then sometimes my eyes aren't so good.

    Don't worry Dwain......I'm always there to help 😀

    The weird thing about this is that now when I SELECT CHAR(25) it doesn't look at all like a quote. So I'm not sure what I selected when I did it.

    Must be too much holiday cheer. :crazy:

    Or is it too much holiday boozing dwain??....I suspect the latter.......:-D:-P

    Honestly, I haven't had a drink in 3 days. Now I think I need one. :hehe:


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • dwain.c (12/24/2012)


    vinu512 (12/24/2012)


    dwain.c (12/24/2012)


    vinu512 (12/20/2012)


    dwain.c (12/20/2012)


    vinu512 (12/20/2012)


    dwain.c (12/19/2012)


    Since quote is also CHAR(25), you can also do this:

    CHARINDEX(CHAR(25), string_column) > 0

    Single Quote is char(39) Dwain......if I am not wrong. 😉

    Looked like a single quote when I SELECTed it but then sometimes my eyes aren't so good.

    Don't worry Dwain......I'm always there to help 😀

    The weird thing about this is that now when I SELECT CHAR(25) it doesn't look at all like a quote. So I'm not sure what I selected when I did it.

    Must be too much holiday cheer. :crazy:

    Or is it too much holiday boozing dwain??....I suspect the latter.......:-D:-P

    Honestly, I haven't had a drink in 3 days. Now I think I need one. :hehe:

    Lets drink then :

    Select Char(34) + Char(67) + Char(72) + Char(69) + Char(69) + Char(82) + Char(83)+ ' ' + Char(68) + Char(87) + Char(65) + Char(73) + Char(78) + Char(34)

    :cool::-D

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • Very cute Vinu! Cheers to you too.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • vinu512 (12/24/2012)


    Lets drink then :

    Select Char(34) + Char(67) + Char(72) + Char(69) + Char(69) + Char(82) + Char(83)+ ' ' + Char(68) + Char(87) + Char(65) + Char(73) + Char(78) + Char(34)

    :cool::-D

    i loveedddd this

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply