Escape delimiter in select query

sh_kk · #1 (**permalink**) 09-17-07, 02:10

Hi All,
Need your help on this query... The query is as follows :

select nm_ctrl_cd from temp where locate('*',translate( nm_ctrl_cd , '*******************************', '~!@#$%^&*()_+}{":?><`-=]['' /., ;' )) > 0

When i execute this query , i am getting the following error:

"SQL0010N The string constant beginning with "' )) > 0
" does not
have an ending string delimiter."

Now this is certainly bcoz of the delimiter.. So the point is that i need to know how to escape a delimiter..

Please help me out with this as i need to fix an issue.

-Thanks

Marcus_A · #2 (**permalink**) 09-17-07, 03:32

Use two consecutive single quotes to replace one single quote.

jsharon1248 · #3 (**permalink**) 09-17-07, 10:45

You didn't specify which version you're using, so I don't know if you're using ASCII or EBCDIC. What you can do is concat the ';' character as a hex string. I think I have the right value for ASCII in this example:

Code:

select col1 from tbl1
where locate('*',translate( col1 
, '*******************************'
, '~!@#$%^&*()_+}{":?><`-=]['' /., ' || x'59' )) > 0;

Internet Services

Web Development

Digital Marketing

Consumer Tech