Home » SQL & PL/SQL » SQL & PL/SQL » Escape special character !! (merged)
Escape special character !! (merged) [message #386932] Tue, 17 February 2009 05:26 Go to next message
vaibhavi
Messages: 21
Registered: April 2008
Junior Member
Hi,

I want to select data from table.
Table contains some column having value containing special characters.
So while selecting from table I want to keep where clause containing special characters.
e.g.
select * from emp
where emp_name in
('abc', 'dk&sd%');

When I try to run this query it consider special character as a bind variable asking for input.

I found one function in php like mysql_escape_string() for this issue.

So is there any way to escape special character and to get data from table ?
(Apart from using set scan on/off).
Re: Escape special character !! (merged) [message #386934 is a reply to message #386932] Tue, 17 February 2009 05:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
set define off
or
set scan off

As stated by the red ball in yellow banner: SEARCH BEFORE POSTING.

Regards
Michel
Re: Escape special character !! (merged) [message #386941 is a reply to message #386932] Tue, 17 February 2009 05:41 Go to previous messageGo to next message
vaibhavi
Messages: 21
Registered: April 2008
Junior Member
hi,

thanks for reply.

I have tried

set define off
or
set scan off.

But is there any other way to overcome this issue ?

Thanks.
Re: Escape special character !! (merged) [message #386945 is a reply to message #386932] Tue, 17 February 2009 05:57 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
Don't use SQLplus. (I'm not actually recommending that but it is the alternative solution).

Just to be clear - it's not oracle that's treating & as a special character - it's SQLplus.

Because & is the default define character in SQLplus.
So set define off is the only thing to do.
Re: Escape special character !! (merged) [message #386946 is a reply to message #386941] Tue, 17 February 2009 06:00 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I'm a bit puzzled: the 'set scan' should work. At least in SQL*Plus, it does. From what environment are you runnng the query? SQL*Plus? SQL*Developer?

another way would be to replace the ampersand with an CHR() function call. If you know that '&' has ascii value 38 you should be able to figure this one out yourself.

MHE
Re: Escape special character !! (merged) [message #386976 is a reply to message #386941] Tue, 17 February 2009 07:51 Go to previous message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
vaibhavi wrote on Tue, 17 February 2009 06:41

set define off
or
set scan off.

But is there any other way to overcome this issue ?



Must you know all way to do it? One way is enough.

How do you open a door? Turn the handle. Knock it down. But is there any other way? Why?
Previous Topic: can i display query result in matrix view (merged 4)
Next Topic: WAY OF STORING DATA IN ORACLE
Goto Forum:
  


Current Time: Wed Dec 07 10:25:24 CST 2016

Total time taken to generate the page: 0.09016 seconds