single qoute

Question

Post reply

single qoute

heero

Mr or Mrs. 500

Points: 521
More actions
August 2, 2002 at 3:05 am

#157887

hi
i have a replace function for single qoute replace to 2 single quotes but when it display the record it show onli one single qoute y izit so??? can anyone explain???
pls enlighten me.. thank...

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

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

GRN Hall of Fame Points: 3260 More actions · Answer 1

This seems to work fine ,

DECLARE @Var varchar(10)

SET @Var = 'TEST' + Char(39) + 'TEST'

PRINT @Var

SET @Var = REPLACE (@Var,CHAR(39),CHAR(39)+CHAR(39))

PRINT @Var

CHAR(39) is the ASCII Value for single quote

are you sure the replace function is working fine ???

heero Mr or Mrs. 500 Points: 521 More actions · Answer 2

hi

for the code tat u provide is to put in the insert sql or which part of the code?

ya i haf problem using replace function when i choose the option in the pulldown menu that i use the replace function it will nt display the record and will said no record found.

pls give me some advice... a million thank....

GRN Hall of Fame Points: 3260 More actions · Answer 3

Hi ,

i'm sorry , i can't quite follow you , are you talking about some front-end replace problem!?!

LabDev-47725 SSC Veteran Points: 235 More actions · Answer 4

Hi...

Days ago, i'got a similar prob.

You must be aware that, when you use a Replace() function to substitute a single-quote to double-quote, AND then do a INSERT or UPDATE to a record, SQL WILL insert the string has single-quoted!

So, if you do a select to that record, you'll see it with a single-quote ...

TRY THIS (Query Analyzer)

--------------------------------

USE tempdb

GO

--SELECT 'Dave O'Brian' -- Wont work!

SELECT 'Dave O''Brian'-- It works

--------------------------------------

See what happends???

Cheers,

@Rodrigues

labdev@netcabo.pt

arodrigues@banifserv.pt

LabDev

labdev@iol.pt

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 5

Each single quote must be doubled for SQL to work a ' repeated as '' is not the same as ". They are two difference characters.

If you replace(@string, '''', '''''') it will work.

declare @a varchar(20)

select @a = 'That''s a test'

select @a = replace('That''s a test', '''', '''''')

select @a

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

SQLBill SSC Guru Points: 51440 More actions · Answer 6

Steve,

It's hard to tell whether you are using single or double quotes in your response. I've noticed that in most threads (not just yours). I suggest emphasizing which type of quotes are being used so there's no confusion.

-SQLBill

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 7

Sorry, I see what you mean.

In the code, the first replace is a single quote, followed by 2 single quotes, then another single quote. Why 4?

1 - open string

2 & 3 - escape the single quote I am looking for by doubling it.

4 closing quote.

The replacement string has an open and close, but two sets of two single quotes, 4 in all. This is to escape the replacements, which are replacing one single quote (doubled to escape it) with two single quotes (each doubled to escape them).

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

heero Mr or Mrs. 500 Points: 521 More actions · Answer 8

hi

why must use

USE tempdb

GO

can anyone explain???

thank you...

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 9

You don't have to use TempDB. The poster was using it as an example. One of the advantages of TempDB is it gets rebuilt when SQL Server is restarted. Therefore, anything we do within TempDB is guaranteed not to be permanent. It isn't required, however, for what you are trying to do.

K. Brian Kelley

bkelley@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@kbriankelley