Results 1 to 9 of 9
  1. #1
    Join Date
    May 2004
    Posts
    105

    Unanswered: syntax to include apostrophe in sql insert

    I have a insert statement but one of the strings contains a apostrophe. If I leave the apostrophe in an error occurs becuase it thinks that it is the end of the string. What is the proper syntax for including apostrophes in a string?

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Double the apostrophes to indicate a literal apostrophe, something like 'That''s all, folks!'

    -PatP

  3. #3
    Join Date
    May 2004
    Posts
    105
    That works. But is there a SQL function that takes these special characters in account. If I have a string that is read in I need to be able to convert it or add what I need to it inorder for it to successfully work without having to manually added another apostrophe. Is there something like this available?

    Thanks,
    Laura

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The problem is that until you get the string to SQL Server, it can't do diddly with the string. You can certainly create functions on the client side that will do this, but they have to be done on the client instead of the server.

    This is provided automagically in some of the ADO libraries, but not in all code or for every client.

    -PatP

  5. #5
    Join Date
    Apr 2004
    Location
    LA, CA
    Posts
    125
    Here is a VB example:

    For i = 1 To Len(strMyString)
    strNextChr = Mid(strMyString, i, 1)
    If strNextChr = "'" Then strNextChr = "''"
    strNewString = strNewString & strNextChr
    Next

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If you set QUOTED_IDENTIFIERS OFF you can enclose all your string values into double-quotes, thus eliminating the need to deal with double apostrophes.

    select "That's all folks" as field1
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    May 2004
    Posts
    105
    Where would I set QUOTED_IDENTIFIERS OFF ?

    -Laura

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    set quoted_identifier off
    select "that's all, folks"
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Dec 2002
    Posts
    1,245
    Or Replace(myString, "'", "''")

    Regards,

    hmscott

    Quote Originally Posted by GDMI.
    Here is a VB example:

    For i = 1 To Len(strMyString)
    strNextChr = Mid(strMyString, i, 1)
    If strNextChr = "'" Then strNextChr = "''"
    strNewString = strNewString & strNextChr
    Next
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •