1

Possible Duplicate:
How do I ignore ampersands in a SQL script running from SQL Plus?

I have an using an sql statement where there is an ampersand(&) explicitly used in the WHERE clause.

e.g. select * from table1 where column1 IN('abc',"'a & b',''def')

In this statement I am getting the values for the IN clause as a comma separated string e.g.string1="'abc',"'a & b',''def'"

which I directly use it to build my sql query. Now when I try to run this query I am unable to execute this query as when I execute it during run time it is asking for a replacement character for ampersand .

how do I change or alter my query so that I can get to use the ampersand

Should I use a regex on the string which I get to manipulate to make it work.As the string(comma seperated value) I am using in the sql query is coming from a text box. the database I am using is Oracle

please help

Community
  • 1
  • 1
sathish kumar
  • 1,187
  • 5
  • 17
  • 21

2 Answers2

2

This Oracle Technology Network forum discussion offers various workarounds for this problem. The simplest, in my opinion, is one of the first suggestions, which is to replace this:

'a & b'

with this:

'a &' || ' b'

but you can look through that page to see if any of the other ideas sounds better to you.

(Though I have to say — I'm a bit worried that you're getting this error. It makes it sound almost as though you were just plugging the user-submitted text right into SQL*Plus . . . which, if it were the case, would be really bad. What would happen if the user submitted '; \n DROP TABLE users;?)

ruakh
  • 175,680
  • 26
  • 273
  • 307
0

if 'a&b' is a Column name, you can use like below.

'A&B'

it will solve the recognizing ampersand problem.
and as @ruakh told using ||, that trick will do work.
These Question1 and Question2 will help You.
for more information check this forum

Community
  • 1
  • 1
Chandra Sekhar
  • 16,256
  • 10
  • 67
  • 90