Dealing with ' and " in strings

Question

Dealing with ' and " in strings

TomT

SSC Eights!

Points: 887
More actions
July 30, 2003 at 12:45 pm

#80924

I am struggling to find a way to deal with cases where one or both of these can show up in a string which I pass to an sp to execute.
E.g.:
INSERT INTO RMAItems ( item_num, order_num, line_item_num, item_desc, ship_to, vend_num, numToReplace, F3RMANum, vend_bill_to, product_num, unit_price_billed, cost_atbill ) VALUES ( 231367, 27690, 4, "SSSI-3/SCSI-3 CABLE MICRO DB68M - 6' W/T'S", 14800, 652, 1, 2654, 2383, '614287', 45.65, 42 )
Works fine if SET QUOTED_IDENTIFIER OFF is set up. However, this string might also be passed:
INSERT INTO RMAItems ( item_num, order_num, line_item_num, item_desc, ship_to, vend_num, numToReplace, F3RMANum, vend_bill_to, product_num, unit_price_billed, cost_atbill ) VALUES ( 231367, 27690, 4, "SSSI-3/SCSI-3 CABLE MICRO DB68M - 6" W/T'S", 14800, 652, 1, 2654, 2383, '614287', 45.65, 42 )
In the first case "SSSI-3/SCSI-3 CABLE MICRO DB68M - 6' W/T'S" is part of the string, in the second case "SSSI-3/SCSI-3 CABLE MICRO DB68M - 6" W/T'S" is passed. The " after the 6 is what is causing my problem.
Any suggestions would be most appreciated.
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 1

Replace any apostrophe (') in the input with two apostrophes and stick with ' for your string delimiter. Or if you like to use quotes (") for the string delimiter, all the apostrophies are no problem but you have to replace quotes in the string with two quotes.

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 2

INSERT INTO RMAItems

(item_num, order_num, line_item_num, item_desc, ship_to, vend_num, numToReplace, F3RMANum,

vend_bill_to, product_num, unit_price_billed, cost_atbill)

VALUES ( 231367, 27690, 4, 'SSSI-3/SCSI-3 CABLE MICRO DB68M - 6" W/T''S', 14800, 652, 1, 2654, 2383,

'614287', 45.65, 42 )

TomT SSC Eights! Points: 887 More actions · Answer 3

Thanks very much for your replies. I'm still a bit confused, though.

If I replace all single quotes with two single quotes ('') then the string

'SSSI-3/SCSI-3 CABLE MICRO DB68M - 6' W/T'S'

would be:

''SSSI-3/SCSI-3 CABLE MICRO DB68M - 6'' W/T''S''

Correct?

If I stick with " as the delimiter and replace all " with two " ("") then

"SSSI-3/SCSI-3 CABLE MICRO DB68M - 6" W/T'S"

would be:

""SSSI-3/SCSI-3 CABLE MICRO DB68M - 6"" W/T'S""

I realize I must be missing something here.

thanks for your assistance and patience...

pepito SSC Journeyman Points: 80 More actions · Answer 4

Just write two times the character you use as string delimiter if it appears in the string you want to insert.

INSERT INTO table(column1,column2) VALUES ('this is ''string'' 1','this is ''string'' 2')

if you use ' as string delimiter.

This will insert the strings:

- this is 'string' 1

- this is 'string' 2

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Hi TomT,

quote:
I am struggling to find a way to deal with cases where one or both of these can show up in a string which I pass to an sp to execute.
E.g.:
INSERT INTO RMAItems ( item_num, order_num, line_item_num, item_desc, ship_to, vend_num, numToReplace, F3RMANum, vend_bill_to, product_num, unit_price_billed, cost_atbill ) VALUES ( 231367, 27690, 4, "SSSI-3/SCSI-3 CABLE MICRO DB68M - 6' W/T'S", 14800, 652, 1, 2654, 2383, '614287', 45.65, 42 )
Works fine if SET QUOTED_IDENTIFIER OFF is set up. However, this string might also be passed:
INSERT INTO RMAItems ( item_num, order_num, line_item_num, item_desc, ship_to, vend_num, numToReplace, F3RMANum, vend_bill_to, product_num, unit_price_billed, cost_atbill ) VALUES ( 231367, 27690, 4, "SSSI-3/SCSI-3 CABLE MICRO DB68M - 6" W/T'S", 14800, 652, 1, 2654, 2383, '614287', 45.65, 42 )
In the first case "SSSI-3/SCSI-3 CABLE MICRO DB68M - 6' W/T'S" is part of the string, in the second case "SSSI-3/SCSI-3 CABLE MICRO DB68M - 6" W/T'S" is passed. The " after the 6 is what is causing my problem.

maybe this thread will clear things a bit

http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=14583

HTH

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

rstone SSCertifiable Points: 6209 More actions · Answer 6

If you are only calling the insert from an application, perhaps a stored procedure with parameters collection can be used. A parameter defined as a string should not need special handling - I think the driver takes care of it. Otherwise, double up your delimiter.

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

TomT SSC Eights! Points: 887 More actions · Answer 7

TomT

SSC Eights!

Points: 887

August 1, 2003 at 3:45 pm

#468294

Thanks to all for your kind assistance...