SIngle Quotes

marksabin · #1 (**permalink**) 01-18-07, 06:35

Hi Guys

I am trying to write a script which will concatenate some text (to form a delete command for a separate db) and a field value. My select statement looks like :

Select Concat ('delete * from adifftable where field1 =','''field1''') from myaudittable where myaudittable_date>trunc(sysdate)-1;

This currently returns:

delete * from adifftable where field1 = 'field1'

What I actually want to see is (i.e. I want to return the value of field1)

delete * from adifftable where field1 = '12345';

Can anyone give me any pointers (as I am tearing my hair out!)

Thanks

r937 · #2 (**permalink**) 01-18-07, 07:08

is this oracle? oracle as a CONCAT function?

Code:

select concat('delete * from adifftable where field1 ='
            , ''''
            , field1
            , '''') 
  from myaudittable 
 where myaudittable_date > trunc(sysdate)-1

marksabin · #3 (**permalink**) 01-18-07, 07:12

Hi

yes using Oracle SQL Plus (8.1.7.0.0). I have tried your suggestion but get:

select concat('delete * from adifftable where field1 ='
*
ERROR at line 1:
ORA-00909: invalid number of arguments

r937 · #4 (**permalink**) 01-18-07, 08:01

well, well, well, invalid number of arguments, eh?

why? because oracle's concat function takes only two arguments!

so try this --

Code:

select concat(
       concat(
       concat('delete * from adifftable where field1 ='
            , '''' )
            , field1 )
            , '''' )
  from ...

marksabin · #5 (**permalink**) 01-18-07, 08:23

Superb - just the job thanks. Virtual pint of beer for you

Peter.Vanroose · #6 (**permalink**) 01-18-07, 11:33

Can't Oracle just do

Code:

select 'delete * from adifftable where field1 =''' || field1 || ''''

?

marksabin · #7 (**permalink**) 01-19-07, 03:31

Yes that works too - thanks Peter (maybe I'm inadvertedly trying to overcomplicate things)

Internet Services

Web Development

Digital Marketing

Consumer Tech