select cname
from yourtable
where (cname like '%a%' or
cname like '%b%' or
... or
cname like '%g%');
orselect cname from yourtable
where translate(cname,'abcdefg','xxxxxxx')!=cname;
Edited by: hm on 06.12.2012 05:06 CREATE TABLE TESTME
(
COL1 VARCHAR2(100 BYTE)
);
insert into testme values('Hoffman' );
insert into testme values('Giovanni' );
insert into testme values('Liu');
insert into testme values('Grass' );
insert into testme values('Clemens' );
insert into testme values('Pereira' );
insert into testme values('Cisneros' );
select * from testme where lower(col1) like '%a%' or lower(col1) like '%g%';
-- testdata:
with yourtable as
(
select 'Hoffman' cname from dual union all
select 'Giovanni' from dual union all
select 'Liu' from dual union all
select 'Grass' from dual union all
select 'Clemens' from dual union all
select 'Pereira' from dual union all
select 'Cisneros' from dual
)
-- query:
select cname, translate(cname,'abcdefg','xxxxxxx')
from yourtable
where translate(cname,'abcdefg','xxxxxxx')!=cname;
vaibhav wrote:You could use TRANSLATE:
but what if the range is large say a-q, it would be time consuming.
Is there any alternate solution?
SQL> select *
2 from testme
3 where translate(col1,'abcdefg','xxxxxxx') != col1
4 /
COL1
----------------------------------------------------------------
Hoffman
Giovanni
Grass
Clemens
Pereira
Cisneros
6 rows selected.
SQL>
SY. vaibhav wrote:Regular expressions.
but what if the range is large say a-q, it would be time consuming.
Is there any alternate solution?
vaibhav wrote:Then you'll have to use lots of LIKE statements as already given. LIKE is ANSI compliant.
Because i want the solution using ansi sql functions.
CREATE TABLE TESTME
(
COL1 VARCHAR2(100 BYTE)
);
insert into testme values('Hoffman' );
insert into testme values('Giovanni' );
insert into testme values('Liu');
insert into testme values('Grass' );
insert into testme values('Clemens' );
insert into testme values('Pereira' );
insert into testme values('Cisneros' );
insert into testme values('zzz');
insert into testme values('r');
select translate(lower(col1),'rstuvwxyz','1'),col1 from testme where replace(translate(lower(col1),'rstuvwxyz','1'),'1','') is not null
vaibhav wrote:Regular expressions are ANSI standard
Because i want the solution using ansi sql functions.