A reader, December 04, 2016 - 3:42 pm UTC
Can we write something using like operator instead of regex function as they use very high cpu.
without Regexp
Rajeshwaran, Jeyabal, December 05, 2016 - 10:24 am UTC
may be something like this ?
demo@ORA12C> with t as
2 (
3 select 'a123' x from dual union all
4 select 'A123' x from dual union all
5 select 'F123' x from dual union all
6 select 'A12d' x from dual )
7 select *
8 from t
9 where replace( translate(x,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','$') ,'$') is null
10 /
X
----
A123
F123
2 rows selected.
demo@ORA12C>
do not meet the need : one char followed by number
gh, December 05, 2016 - 10:55 am UTC
with t as
(
select 'a123' x from dual union all
select 'A123' x from dual union all
select 'FF123' x from dual union all
select 'F123' x from dual union all
select 'A12d' x from dual )
select *
from t
where coalesce ( trim(translate(substr(x,1,1),'ABCDEFGHIJKLMNOPQRSTUVWXYZ',' ')), trim( translate(substr(x,2),'0123456789',' ')) )is null