Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Finding a quote in a string Expand / Collapse
Author
Message
Posted Wednesday, December 19, 2012 2:47 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 1:19 PM
Points: 164, 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 quote
select @dest_city = replace(@dest_city,'''',' ')
Post #1398681
Posted Wednesday, December 19, 2012 2:59 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:46 PM
Points: 2,037, Visits: 3,049
string_column LIKE '%''%'

or:

CHARINDEX('''', string_column) > 0


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1398685
Posted Wednesday, December 19, 2012 3:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 1:19 PM
Points: 164, Visits: 231
Thank you Scott.
Post #1398686
Posted Wednesday, December 19, 2012 5:33 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:30 PM
Points: 3,627, Visits: 5,273
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!
Post #1398719
Posted Thursday, December 20, 2012 2:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, August 24, 2014 5:44 AM
Points: 1,125, Visits: 1,589
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
Post #1398862
Posted Thursday, December 20, 2012 4:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:06 PM
Points: 5,047, Visits: 11,797
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1398901
Posted Thursday, December 20, 2012 10:02 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:30 PM
Points: 3,627, Visits: 5,273
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!

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!
Post #1399181
Posted Thursday, December 20, 2012 10:19 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, August 24, 2014 5:44 AM
Points: 1,125, Visits: 1,589
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
Post #1399182
Posted Friday, December 21, 2012 4:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:17 AM
Points: 2,434, Visits: 7,513
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



Not a DBA, just trying to learn

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/



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 nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1399320
Posted Monday, December 24, 2012 12:52 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:30 PM
Points: 3,627, Visits: 5,273
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.



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!
Post #1399832
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse