Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104

    Unanswered: escaping an apostrophe in MSSQL

    Hi, i'm having problems executing the following

    SET @SQLAH = 'SELECT sub_id WHERE '
    SET @SQLAH = @SQLAH + 'VENUE_TYPE = Hotel'
    EXEC(@SQLAH)

    Its getting stuck at Hotel. I realise that it should include an apostrophe either side like so:

    ..
    SET @SQLAH = @SQLAH + 'VENUE_TYPE = 'Hotel' '
    ..

    But this escapes the string, how would i escape an apostrophe in a string?

    I thought maybe:

    SET @SQLAH = @SQLAH + 'VENUE_TYPE = \'Hotel\' '

    But no joy

    Thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    As far as I know MS SQL is using the ANSI standard for that: two single quotes:

    SET @SQLAH = @SQLAH + 'VENUE_TYPE = 'Hotel'''

    (Don't know if it works inside a procedure though)

  3. #3
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    hi

    nope, it doesnt work - i'm using a stored procedure

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SET @SQLAH = @SQLAH + 'VENUE_TYPE = ''Hotel'' '
    -PatP

  5. #5
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    thats the badger! cheers

Posting Permissions

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