Re: an 'ALL_FIELDS' equivalent

Not in any straightforward way - if you want to experiment try select f1,f2,f3,f4 from table_1 where
f1||f2||f3||f4 like '%testvalue%'

the || is the concatenation operator so you are building a string of all the returned column values and then searching that string for the substring containing the value you want. This will NOT work if the returned values have more than one instance of the value you are seeking - also if any of the returned values are numeric you need to convert it before concatenation ( i.e. where f1||to_char(f2)|| etc)

It will be slow if there are lots of fields, so its not a very practical solution for large data tables...

Hope it gives you an idea of what to do.

John Greco
Oracle DBA
Reply to john.greco_at_dot.state.mn.us

Kal Khatib <kal_at_gene.COM> wrote:

>I don't know if it is possible, but..
>I'm trying to translate this psuode statement into SQL
>
>select * from table_1
>where (any of the fields selected) like ('%somestring%')
>
>OR
>
>select f1,f2,f3,f4,f5,f6 from table_1
>where (any of the fields selected ) like ...etc
>
>is it possible to translate the 'any of the fields selected' to SQL?
>
>thanks for any help
>Kal.
>--
>http://members.tripod.com/~kalk
>
>
Received on Fri Feb 13 1998 - 00:00:00 CST