Home » SQL & PL/SQL » SQL & PL/SQL » look for & in string
look for & in string [message #362836] Thu, 04 December 2008 04:36 Go to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
Hi,
I am trying to find Text that contains & in it but I cannot.

for example; I am trying to find value 'R&D' in a column. It is prompting to substitute a value.

Thanks,
Mahesh
Re: look for & in string [message #362840 is a reply to message #362836] Thu, 04 December 2008 04:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
1) Don't do it in SQL*Plus
2) If you must use SQL*Plus, use SET DEFINE OFF to disable variable substitution, or SET DEFINE "any character" to change the substitution chr from & to something else
Re: look for & in string [message #362859 is a reply to message #362836] Thu, 04 December 2008 05:51 Go to previous messageGo to next message
rajy_salim
Messages: 204
Registered: January 2008
Location: Beirut - Lebanon
Senior Member
select <your_column> from <your_table> where <your_column> = 'R' || chr(38) || 'D'

This works either you use SQL*PLUS or not, and if you use it, you don't have to SET DEFINE OFF.

Rajy

[Updated on: Thu, 04 December 2008 05:55]

Report message to a moderator

Re: look for & in string [message #362914 is a reply to message #362859] Thu, 04 December 2008 09:00 Go to previous messageGo to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
Thanks Rajy.

I am passing this value from the application to the database and wondering if there is something at the DB level?
Re: look for & in string [message #362916 is a reply to message #362836] Thu, 04 December 2008 09:02 Go to previous messageGo to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
Hey JROWBottom,

Whats the other way apart from not using the SQL *Plus
Re: look for & in string [message #362917 is a reply to message #362916] Thu, 04 December 2008 09:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Whats the other way apart from not using the SQL *Plus

Quote:
2) If you must use SQL*Plus, use SET DEFINE OFF to disable variable substitution, or SET DEFINE "any character" to change the substitution chr from & to something else

Is this not the answer?

Regards
Michel
Re: look for & in string [message #362919 is a reply to message #362916] Thu, 04 December 2008 09:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Use TOAD, Pl/Sql developer. SQL Developer - anything that's willing to just be an SQL interface, and won't spend half it's time pretding to be a piece of report writing software.
Re: look for & in string [message #362920 is a reply to message #362836] Thu, 04 December 2008 09:04 Go to previous messageGo to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
Hey JROWBottom,

Whats the other way apart from not using the SQL *Plus
Re: look for & in string [message #362921 is a reply to message #362920] Thu, 04 December 2008 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 04 December 2008 09:04
Quote:
Whats the other way apart from not using the SQL *Plus

Quote:
2) If you must use SQL*Plus, use SET DEFINE OFF to disable variable substitution, or SET DEFINE "any character" to change the substitution chr from & to something else

Is this not the answer?

Regards
Michel


Re: look for & in string [message #362922 is a reply to message #362919] Thu, 04 December 2008 09:06 Go to previous messageGo to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
I used toad, it is asking me to substitute some value..it looks like it is assuming &D ( in text R&D ) as a parameter.
Re: look for & in string [message #362923 is a reply to message #362921] Thu, 04 December 2008 09:08 Go to previous messageGo to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
can we change this at oracle session level? because our application connects to the DB and fires this query..so i am thinking it would be best set someting like this at DB level.

Thanks!
Re: look for & in string [message #362924 is a reply to message #362922] Thu, 04 December 2008 09:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Oh yes - it does.
Forgot that.
SET DEFINE OFF should fix that in TOAD too.
Re: look for & in string [message #362926 is a reply to message #362836] Thu, 04 December 2008 09:29 Go to previous messageGo to next message
knicely87
Messages: 25
Registered: December 2008
Location: Pittsburgh, PA
Junior Member
You can also just use concatenation to escape the &:

select <your_column> from <your_table> where <your_column> = 'R' || '&' || 'D';


Thanks,
Jim
Re: look for & in string [message #362929 is a reply to message #362836] Thu, 04 December 2008 09:37 Go to previous messageGo to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
Thank alot guys for the answers, just wondering how can set the SET DEFINE OFF in session or oracle environment variable..or someway so that when oracle is started the Define is set to Off.

Thanks!!
Mahesh
Re: look for & in string [message #362932 is a reply to message #362929] Thu, 04 December 2008 10:18 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
'&' has nothing to do with oracle database. It is do with sql*plus client. I hope this is clear. So a database parameter or alter session will not help you. There is a file called glogin.sql which gets invoked when you invoked when sqlplus gets executed. You could give this command in that file. Search in google for more information about glogin.sql.

Regards

Raj
Re: look for & in string [message #362933 is a reply to message #362836] Thu, 04 December 2008 10:20 Go to previous messageGo to next message
Mahesh P
Messages: 69
Registered: September 2004
Member
Oh okay. i got it.

Thanks Raj..
Re: look for & in string [message #363345 is a reply to message #362933] Sat, 06 December 2008 00:13 Go to previous messageGo to next message
raja.or.king
Messages: 24
Registered: November 2008
Location: GJ 5
Junior Member
Hi all,

in higher level languages like C/C++,
there is a escape sequence to change the default behaviour.

Isn't there any way to do like that in SQL *plus?
Re: look for & in string [message #363346 is a reply to message #363345] Sat, 06 December 2008 00:29 Go to previous message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
set escape

Regards
Michel
Previous Topic: Problem querying node values from a XMLType table defined from a schema.
Next Topic: Impace of statistics collection
Goto Forum:
  


Current Time: Tue Dec 06 08:42:08 CST 2016

Total time taken to generate the page: 0.13871 seconds