How to handle "select a,b where c=' 1 & 2' in a 'hard coded' odbc query

[jhaxo]

I am maintaining an asp page that builds a select query as a plain old string and executes it using oracle odbc.

There is basically too much code to change over to stored proccedures or other nice means.

How can I escape a & ? Using sql+ there is an escape on command, is there a similar concept for odbc? Is this an odbc question rather than oracle?

[debasisdas]

can you kindly post the query for my reference.

[jhaxo]

can you kindly post the query for my reference.

sorry i did not get an email notification.
That is really the query.

"select a,b from mytable where c=' 1 & 2' ".
c is a varchar, a and b are columns . I don't have the freedom to do a sp or p, or a parameterized command. And worse still, it has to work with Oracle and Access.

I did discover there is an escape concept in sql+ that you can turn on and then \& is interpreted as a character and not a place holder for a parameter.

[amitpatel66]

sorry i did not get an email notification.
That is really the query.

"select a,b from mytable where c=' 1 & 2' ".
c is a varchar, a and b are columns . I don't have the freedom to do a sp or p, or a parameterized command. And worse still, it has to work with Oracle and Access.

I did discover there is an escape concept in sql+ that you can turn on and then \& is interpreted as a character and not a place holder for a parameter.

If you change your query to something like this:

 
select a,b from mytable where c IN ('1','2')
 
 

[jhaxo]

If you change your query to something like this:

 
select a,b from mytable where c IN ('1','2')
 
 

no, sorry c is a varchar that has the literal value '1 & 2'.
like 'burger & fries'.

[amitpatel66]

no, sorry c is a varchar that has the literal value '1 & 2'.
like 'burger & fries'.

Try this way:

 
SQL> SET SCAN OFF
SQL> declare
  2  c varchar2(20) := '1 & 2 & 3';
  3  v_output VARCHAR2(2);
  4  s_sql VARCHAR2(200);
  5  BEGIN
  6  c:= CHR(39)||REPLACE(c,' & ',',')||CHR(39);
  7  s_sql:= 'SELECT ''x'' FROM DUAL WHERE ''x'' NOT IN ('||c||')';
  8  EXECUTE IMMEDIATE s_sql INTO v_output;
  9  dbms_output.put_line('Format of IN parameter:'||c);
 10  dbms_output.put_line('Output using Formatted IN:'||v_output);
 11  end;
 12  /
Format of IN parameter:'1,2,3'
Output using Formatted IN:x
 
PL/SQL procedure successfully completed.
 
SQL> 
 
 

Remember in the above example the 2 single colons are used in the select statement and not a double quote

[amitpatel66]

Something like this also will work:

 
SQL> SET SCAN OFF
SQL> select * from dual where 'x' not in (REPLACE('1 & 2',' & ',','))
  2  /
 
D
-
X
 
SQL>