## Finding a quote in a string

 Author Message Rog Saber Ten Centuries Group: General Forum Members Points: 1072 Visits: 231 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 quoteselect @dest_city = replace(@dest_city,'''',' ') ScottPletcher SSC-Forever Group: General Forum Members Points: 47064 Visits: 8078 string_column LIKE '%''%'or:CHARINDEX('''', string_column) > 0 SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them. Rog Saber Ten Centuries Group: General Forum Members Points: 1072 Visits: 231 Thank you Scott. dwain.c SSC-Forever Group: General Forum Members Points: 44713 Visits: 6431 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!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?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables vinu512 SSCrazy Eights Group: General Forum Members Points: 8637 Visits: 1626 dwain.c (12/19/2012)Since quote is also CHAR(25), you can also do this:CHARINDEX(CHAR(25), string_column) > 0Single Quote is char(39) Dwain......if I am not wrong. ;-) Vinu VijayanFor better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden ;-) Phil Parkin SSC Guru Group: General Forum Members Points: 129677 Visits: 22605 Another way:where len(Col) > len(replace(Col, '''', '')) Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot. dwain.c SSC-Forever Group: General Forum Members Points: 44713 Visits: 6431 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) > 0Single 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!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?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables vinu512 SSCrazy Eights Group: General Forum Members Points: 8637 Visits: 1626 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) > 0Single 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 :-D Vinu VijayanFor better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden ;-) Cadavre SSC-Insane Group: General Forum Members Points: 22232 Visits: 8519 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) > 0Single 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 :-P`SELECT N, CHAR(N) AS CharResultFROM (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 CharResultFROM (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 Forever trying to learnFor 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/If you litter your database queries with nolock query hints, are you aware of the side effects?Try reading a few of these links...(*) Missing rows with nolock(*) Allocation order scans with nolock(*) Consistency issues with nolock(*) Transient Corruption Errors in SQL Server error log caused by nolock(*) Dirty reads, read errors, reading rows twice and missing rows with nolockCraig Wilkinson - Software EngineerLinkedIn dwain.c SSC-Forever Group: General Forum Members Points: 44713 Visits: 6431 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) > 0Single 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 :-DThe 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. My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!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?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables