ESCAPE quote

  • I have  an insert like the following :

    INSERT  INTO Tbl_temp_stat  VALUES (@county,460,50,0 ) which translates to

    INSERT  INTO TblJis_temp_stat  VALUES (' St.Mary's County',460,50,0 ) which gives me an error

    because of the quote . I am unable to use a function like REPLACE to substitute the single quote with 2 single quotes. Gives me an error again .

    Any ideas will be appreciated.

     

  • declare @cmd varchar(255)

    declare @county varchar(255)

    select  @county = "St.Mary's County"

    select @cmd = 'INSERT  INTO Tbl_temp_stat  VALUES ("' + @county + '",460,50,0 )'

    exec (@cmd)

     

  • It still gives me the following error  :

    Server: Msg 207, Level 16, State 3, Line 4

    Invalid column name 'St.Mary's County'.

  • Add "set quoted_identifier off" as first line.

  • This works fine from Query Analyzer but in my Stored procedure it still gives the same error as before. I think the EXEC does not recognize the  "set quoted_identifier off" from the previous line.  Is there any other way I can do this ?

     

  • I don't know what's your problem,

    This works for me :

    Declare @data varchar(20)

    set @data = 'St.Mary''s County'

    insert into test VALUES (@data)

     

    Are you using dynamic sql ? --> take a look at QUOTENAME 


    * Noel

  • I am using dynamic SQL, that is why I cannot change the variable as you have  done for @data.

    But QUOTENAME worked great ! Thanku.

  • There is just one thing to watch out for when using quotename(). The input parameter is a nvarchar(129)

    On SQL 2k you can use

       CREATE FUNCTION quotestring(@str nvarchar(1998)) RETURNS nvarchar(4000) AS

       BEGIN

          DECLARE @ret nvarchar(4000),

                  @sq  char(1)

          SELECT @sq = ''''

          SELECT @ret = replace(@str, @sq, @sq + @sq)

          RETURN(@sq + @ret + @sq)

       END

     

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

  • When & where possible, I prefer to replace the pesky quote ASCII code CHAR(39),

    with CHAR(146) or CHAR(145) and put an end to the madness of chasing and reacting

    to the many different problems associated with it.

  • Try something like this....

    SET @var = REPLACE(@strWord,'''','''''')

    Then build the rest of the string.




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Viewing 10 posts - 1 through 9 (of 9 total)

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