Post reply

Format SQL string problem with single quote

  • October 22, 2008 at 12:40 am

    #154835

    Working with SQL server 2000 in a stored procedure.

    I'm trying to format SQL so that I can execute this SQL in a stored procedure.

    SET @SQL = N'UPDATE TblPostAcademicEducation set ' + @col + ' = ''' + @record + ''' where formResultID = ' + @FRID

    @col is the name of a column, comes from a cursor

    @record is the value of a certain record in that column

    @FRID is an ID column

    The problem I'm facing is that the value of @record (string) sometimes contains a single quote, resulting in an error when I try to format the string.

    I have been googling a lot, but cannot find the exact same problem I have.

    Found a replace option, would be something like replace(@record, ''', ''''), that I should replace a single quote with two single quotes, but that doesn't seem to do the trick.

    Does anyone have an idea on how the resolve this?

    Thanks.

    Vera

  • October 22, 2008 at 3:17 am

    #888003

    Use replace(@record,'''','''''')

    Madhivanan

    Failing to plan is Planning to fail

  • October 22, 2008 at 3:23 am

    #888004

    Thank you, that works.

  • October 23, 2008 at 7:47 am

    #888597

    Vera (10/22/2008)

    Thank you, that works.

    welcome 🙂

    Madhivanan

    Failing to plan is Planning to fail

Viewing 4 posts - 1 through 3 (of 3 total)

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