ESCAPE quote

Question

Post reply

ESCAPE quote

thriveni

Ten Centuries

Points: 1104
More actions
February 6, 2004 at 9:02 am

#163004

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.

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

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

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

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)

thriveni Ten Centuries Points: 1104 More actions · Answer 2

It still gives me the following error :

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

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

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

Allen Cui-55137

SSC Guru

Points: 51650

February 6, 2004 at 9:27 am

#493113

Add "set quoted_identifier off" as first line.

thriveni Ten Centuries Points: 1104 More actions · Answer 4

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 ?

noeld SSC Guru Points: 96590 More actions · Answer 5

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

thriveni Ten Centuries Points: 1104 More actions · Answer 6

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

But QUOTENAME worked great ! Thanku.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

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]

Mongo_KS SSC Veteran Points: 210 More actions · Answer 8

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.

gljjr SSChampion Points: 10080 More actions · Answer 9

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.