Re: Dumb Question, how to insert ' into varchar2 field

From: Norman Dunbar <Norman_at_Dunbar-it.co.uk.REMOVETHIS>
Date: Tue, 15 Feb 2005 17:37:16 +0100
Message-ID: <cutbn9$93i$5$8302bc10@news.demon.co.uk>

Hi Perm,

try :

insert into user.table values ( 'some text containing ''. some more text' );

The single quote is doubled to two single quotes in order that Oracle acepts it.

Under 10g, there's a new method - which I can't quite remember, however, if I do it wrong here, someone will correct me - which looks similar to something like this :

Q'<delim> text <delim>'

as in :

insert into table values ( Q'"some text with a ' in it"' );

The delimiter can be any character which doesn't appear in the quotes text. The 'Q' can be lower case as well.

The manual gives the following examples :

q'!name LIKE '%DBMS_%%'!'
q'<'So,' she said, 'It's finished.'>'
q'{SELECT * FROM employees WHERE last_name = 'Smith';}'

nq'ï Ÿ1234 ï'
q'"name like '['"'

I'm suspicious of the second and third ones myself as the delimiter is different at the end of the string from that used at the start of the string viz < and > or { and }. I don't have my 10g laptop set up at the moment so I can't test, but I suspect this is a bug in the SQL manual under TEXT literals and the alternate quoting system.

I personally would stick with a double quote or a ! myself as I'd be inclined to do a similar mistake myself with <> or () or {} or [] - I'd always want a matching brace.

On the other hand, if the above isn't an error, then I'd be happy to use () or [] etc.

Cheers,
Norm. Received on Tue Feb 15 2005 - 10:37:16 CST