Re: Need to search for data in any Field

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 23 Feb 2002 01:45:34 GMT
Message-ID: <Xns91BE1C16F498Fgnuegischgnueg@130.133.1.4>

> I'm interested in looking for the work "Activated" in a table called
> Customers that has over 35 fields.  What is the easies way to do this
> without having to use the each different field in a query?

Try this one (CUSTOMERS needs to be in your schema. If not, use all_tab_columns instead of user_tab_columns)

---------->8--------->8--------->8--------------------

set feedback off
set pagesize 0
drop table customers;
create table customers (
  a01 varchar2(20), a02 varchar2(20), a03 varchar2(20), a04 varchar2(20), a05 varchar2(20),
  a06 varchar2(20), a07 varchar2(20), a08 varchar2(20), a09 varchar2(20), a10 varchar2(20),
  a11 varchar2(20), a12 varchar2(20), a13 varchar2(20), a14 varchar2(20), a15 varchar2(20),
  a16 varchar2(20), a17 varchar2(20), a18 varchar2(20), a19 varchar2(20), a20 varchar2(20),
  a21 varchar2(20), a22 varchar2(20), a23 varchar2(20), a24 varchar2(20), a25 varchar2(20),
  a26 varchar2(20), a27 varchar2(20), a28 varchar2(20), a29 varchar2(20), a30 varchar2(20),
  a31 varchar2(20), a32 varchar2(20), a33 varchar2(20), a34 varchar2(20), a35 varchar2(20)
);

insert into customers values (
'a', 'a', 'activ', 'a', 'a',
'activted', 'a', 'a', 'a', 'aaaccttivvaatteedd',
'a', 'a', 'a', 'a', 'a',
'a', 'a', 'a', 'a', 'a',
'a', 'a', 'a', 'a', 'a',
'a', 'a', 'a', 'a', 'a',
'a', 'a', 'a', 'a', 'a');

insert into customers values (
'oracle buzzwords', 'fgac', 'cbo', 'rbo', 'cost',
'Bactivadetlkdkj', 'hello', 'B', 'B', 'B',
'B', 'B', 'oracle', 'java', 'B',
'B', 'server', 'active directory', 'xml', 'B',
'load', 'sql/plus', 'buzzwords', 'B', 'B',
'oci', 'B', 'B', 'B', 'B',
'', 'B', 'B', 'B', 'B');

insert into customers values (
'foo', 'foo', 'foo', 'twofoo', 'onefoo',
'foo', 'fooxxxx', 'foo', 'tryfoo', 'foo',
'foo', 'foo', 'foo', 'foo', 'testfoo',
'foo', 'activefoo', 'inactivefoo', 'foo', 'foohoo',
'foo', 'foobaractivater', 'foo', 'foo', 'foo',
'foo', 'javafoo', 'foo', 'foo', 'foo',
'foo', 'foo', 'foo', 'foo', 'foo');

insert into customers values (
'bar', 'bar', 'bar', 'bar', 'bar',
'baactivatetr', 'bar', 'bar', 'bar', 'bar',
'bar', 'bar', 'bar', 'bar', 'bar',
'bar', 'bar', 'bar', 'bar', 'baractivear',
'bar', 'bar', 'baractiv', 'bar', 'bar',
'bar', 'bar', 'bar', 'bar', 'bar',
'bar', 'bactivaar', 'bar', 'bar', 'bar');
insert into customers values (
'baz', 'baz', 'baz', 'baz', 'baz',
'active or not', 'baztivated', 'baz', 'baz', 'baz',
'is it activare', 'baz', 'baz', 'bazactivare', 'baz',
'baz', 'baz', 'baz', 'baz', 'baz',
'baz', 'baz', 'baz', 'hereisactivated!', 'baz',
'baz', 'bactivaaz', 'baz', 'baz', 'baz',
'baz', 'activetadbaz', 'baz', 'baz', 'baz');

spool findit.sql

select 'set feedback off' from dual;
select 'prompt' from dual;
select 'prompt ****************************************' from dual;
select 'prompt ACTIVATED is found in the following rows' from dual;
select 'prompt ****************************************' from dual;
select 'select * from customers where ' from dual;
select 'upper(' || column_name || ') like ''%ACTIVATED%''  or ' from 

user_tab_columns
where table_name='CUSTOMERS' and column_id < (select max(column_id) from user_tab_columns where table_name =
'CUSTOMERS');

select 'upper(' || column_name || ') like ''%ACTIVATED%''; ' from user_tab_columns
where table_name = 'CUSTOMERS' and column_id = (select max(column_id) from user_tab_columns where table_name =
'CUSTOMERS');

spool off
@findit.sql

---------->8--------->8--------->8--------------------

hth
Rene

-- 
Recherchen im schweizerischen Handelsregister: 
http://www.adp-gmbh.ch/SwissCompanies/Search.php3

Received on Fri Feb 22 2002 - 19:45:34 CST