Post reply

Dealing with ' and " in strings

  • 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

  • July 30, 2003 at 4:52 pm

    #467865

    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.

  • July 30, 2003 at 7:40 pm

    #467876

    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 )

  • July 30, 2003 at 10:22 pm

    #467880

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

  • July 31, 2003 at 1:39 am

    #467894

    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

  • July 31, 2003 at 4:51 am

    #467920

    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://qa.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]

  • July 31, 2003 at 9:29 am

    #468025

    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.

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

  • August 1, 2003 at 3:45 pm

    #468294

    Thanks to all for your kind assistance...

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

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