Home » SQL & PL/SQL » SQL & PL/SQL » single quote substitution
single quote substitution [message #258170] Fri, 10 August 2007 08:11 Go to next message
greekozz
Messages: 8
Registered: August 2007
Junior Member
ok guys i have a database with names that include single quotes
and i want to query this database by the name that the user inputs.
And everything works until the user enters a name with single quote.
Besides telling the user whenever he enters a single quote to enter two is there any other way?

example
ACCEPT LastName CHAR PROMPT 'Customer name:'
select replace('&lastName', '''', '''''')
from dual


SQL> @nata
Customer name:BOB'S
old   1: select replace('&lastName', '''', '''''')
new   1: select replace('BOB'S', '''', '''''')
ERROR:
ORA-01756: quoted string not properly terminated
Re: single quote substitution [message #258175 is a reply to message #258170] Fri, 10 August 2007 08:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select q'!bob's!' n from dual;

N
-----
bob's

Regards
Michel
Re: single quote substitution [message #258183 is a reply to message #258170] Fri, 10 August 2007 09:03 Go to previous messageGo to next message
ora4dev
Messages: 7
Registered: October 2005
Junior Member
Try this:

SQL> select 'bob''s' from dual;

'BOB'
-----
bob's


Raju M
Re: single quote substitution [message #258185 is a reply to message #258175] Fri, 10 August 2007 09:18 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Michle i have run the query which given by u.

select q'!bob's!' n from dual;


but i am getting error.
ORA-01756: quoted string not properly terminated


oracle version is 9.0.2.6

--Yash
Re: single quote substitution [message #258195 is a reply to message #258170] Fri, 10 August 2007 09:47 Go to previous messageGo to next message
greekozz
Messages: 8
Registered: August 2007
Junior Member
old 1: select replace('&lastName', '''', '''''')
new 1: select replace('BOB'S', '''', '''''')


Micheal you are the man!
But i need something different
i want what is contained in the &lastname variable to be accepted
as a hall.
i don't wont the single quote to be interpeted in any other way than as a part of Bob's name

i tried to put it inside !! "" or even {}
but it still doesn't work.

Is there way to change my initial example in a way that it works?

Thanks again.
Re: single quote substitution [message #258202 is a reply to message #258185] Fri, 10 August 2007 10:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yash, it is a 10g syntax.

greekozz, if you use the syntax I gave you don't need to replace the ' to use it.

SQL> host type t.sql
ACCEPT LastName CHAR PROMPT 'Customer name:'
select q'!&lastname!' n from dual;


SQL> @t
Customer name:bob's
N
-----
bob's

1 row selected.

Regards,
Michel
Re: single quote substitution [message #258259 is a reply to message #258170] Fri, 10 August 2007 13:25 Go to previous messageGo to next message
greekozz
Messages: 8
Registered: August 2007
Junior Member
Hey Michael my bad!
Now i got back to my lovely monitor i noticed that it was a q and not a g as i had originally thought.

It worked like a charm!! Thanks a lot.

The books i have are about 9i version . So i don't know what this q stands for, maybe it's on a further chapter.

you wouldn't happen to know how can this be done in 9i now would you?

Anyways u rock!
Re: single quote substitution [message #258264 is a reply to message #258259] Fri, 10 August 2007 13:43 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know how to do it in 9i in a general manner as in 10g but there is surely a solution specific for each case.

Regards
Michel
Previous Topic: Oracle 10, Pro/C, SELECT...GROUP BY... ORDER BY problem
Next Topic: Why package can't see v$session?
Goto Forum:
  


Current Time: Sun Dec 11 07:53:20 CST 2016

Total time taken to generate the page: 0.10113 seconds