Oracle SQL regular expression - check for numbers or special characters

We all know the regular expression syntax is fast and you can do amazing things with little code snippets. But we do know as well that whenever you need it then you have no idea how to write it down. To be able to find a solution for your problem you use the WWW.

So here you have another example you may need one day. :)
I had to check a string for special characters and numbers. In those cases the rows should be highlighted.

Here is a simple example how to do it:

select 
 rn, 
 val as txt, 
 case 
  when regexp_like(val, '[[:cntrl:]]| |[[[:digit:]]|[[:punct:]]') 
  then 'Error' 
  else 'Ok'
  end as type
from 
(
 select 1 as rn, '123' as val from dual
 union all
 select 2 as rn, 'xxxxx' as val from dual
 union all
 select 3 as rn, 'acbAaaBBB' as val from dual
 union all
 select 4 as rn, 'acb Aaa BBB' as val from dual
 union all
 select 5 as rn, '#abc' as val from dual
)
order by rn

Post Tags: