multiple times escaping "&" in oracle sql query

Question

I am writing one sql query as below :-

select a, b, c
from table_name
where b in (
'some string & some string'
,'some other string & some other string'
)

Things I have tried :-

Tried using SET DEFINE OFF. It seemed to work fine but its use is restricted to sql developer only, which is not my requirement. As i need to export this query to other tool for creating report.
Tried using chr(38) like

'some string'|| chr(38) ||'some string'
,'some other string'|| chr(38) ||'some other string'

But it works fine when I use it one time. Using chr(38) multiple times is throwing error.

Any leads over here is aprreciable. Thanks in advance.

I disagree!! You can use CHR(38) as many time as you want in your string. — Popeye, Jun 23 '20 at 09:15
`Using chr(38) multiple times is throwing error.` --- Which error it is throwing? — Popeye, Jun 23 '20 at 09:18
SELECT * FROM DUAL WHERE 'a' || CHR(38) || 'a' IN ('a' || CHR(38) || 'a' , 'y' || CHR(38) || 'y'); — harun ugur, Jun 23 '20 at 11:17

score 0 · Answer 1 · answered Jun 23 '20 at 09:18

You can use the chr(38) as many times as you want. In your example, I can see that spaces are not managed properly. There is space before and after &.

Your actual string: 'some string & some string'

Your string with concatenation: 'some string'|| chr(38) ||'some string' --> 'some string&some string' --> You can see that spaces before and after & is missing in your concatenated string.

so this space must be there after concatenation. Following will help you

SQL> select *
  2  from dual
  3  where 'some string '|| chr(38) ||' some string' in (
  4  'some string '|| chr(38) ||' some string' -- see the space before and after concatanation
  5  ,'some other string ' || chr(38) ||' some other string'
  6  );

D
-
X

SQL>

multiple times escaping "&" in oracle sql query

1 Answers1