wamt to eliminate ampersand in a query

[prasen999]

hi folks,

i have a query like the one below :-


select a.amount,a.clear_memono,to_char(a.clear_memodate,' dd/mm/yyyy'),to_char(a.sancdate,'dd/mm/yyyy'),b.description
from goi a,goi_master b
where
sanc_letterno='No.15013/2180/DCH/M&E'

and sancdate=to_date('06/2003','mm/yyyy')
and a.code=b.code


i am using this as an inline query in a jsp code. due to the ampersand,the query is failing. i tried to use the replace function to eliminate the ampersand,but it didn't work.
Any suggestions folks?

thanks,
Prasen

[skhanal]

Assuming it is Oracle, use CHR(38) instead of &

select a.amount,a.clear_memono,to_char(a.clear_memodate,' dd/mm/yyyy'),to_char(a.sancdate,'dd/mm/yyyy'),b.description
from goi a,goi_master b
where
sanc_letterno='No.15013/2180/DCH/M'||chr(38)||'E'

[prasen999]

hi,
thanks for the suggestion. but actually i found out the solution doing a little trial and error.

the sanctionletterr no is an input parameter to my application.
the db column sanc_letterno had ampersand in its values. so i manipulated the incoming parameter in my jsp so that iht comes as
'No.15013/2180/DCH/M''&''E' and then i used the query below

select a.amount,a.clear_memono,to_char(a.clear_memodate,' dd/mm/yyyy'),to_char(a.sancdate,'dd/mm/yyyy'),b.description
from goi a,goi_master b
where sanc_letterno= replace('No.15013/2180/DCH/M''&''E','''&''','&')
and to_char(sancdate,'mm/yyyy')='06/2003'
and a.code=b.code

this worked.
can u suggest me some documents where i can get the full knowledge of the usage of quotes. that'll b great help.

btw, what is the funda of CHR(38) that u suggested.

thanks and regards,
Prasen

[skhanal]

That is ASCII value for &.