Skip to Main Content
  • Questions
  • Regular expression to find rows with characters that are not letters, numbers or keyboard symbols

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: April 11, 2019 - 9:08 am UTC

Last updated: April 11, 2019 - 9:08 am UTC

Version:

Viewed 10K+ times! This question is

You Asked

Hi,

This is the table t2 I have,

Sl.No. Junk
1. Cigarette use – last used 4/2017 – NS at best;
2. test]]]]]]]
3. [[[[test
4. [CDATA[]]

Now I want to write query to get only the 1st row which have junk character i.e. –

I try the query below,

select t.* from t2 t where regexp_like(junk, '[^a-zA-Z0-9~!@#$%^&*()_+-=;{}\|'':"?,./` ]');

but it gives all 4 rows as I didn't put [ and ] in regex, and if use [ and ] in regex as below,

select t.* from t2 t where regexp_like(junk, '[^a-zA-Z0-9~!@#$%^&*()_+-=;{}\|'':"?,./`\]\[ ]');

it will not fetch any rows

How can I write the query to get only those data which if it contains character which is not a-Z, A-Z, 0-9 and all those special characters in keyboard i.e. ~!@#$%^&*()_+`-={}|[]\:";'>?,./

and Chris said...

You can use the POSIX character class [:punct:] to match symbols:

SQL> with rws as (
  2    select 'Cigarette use – last used 4/2017 – NS at best;' s from dual
  3    union all
  4    select 'test]]]]]]]' s from dual
  5    union all
  6    select '[[[[test' s from dual
  7    union all
  8    select '[CDATA[]]' s from dual
  9  )
 10    select * from rws
 11    where  regexp_like(s, '[^\s[:alnum:][:punct:]]');

S
----------------------------------------------
Cigarette use ? last used 4/2017 ? NS at best;



Is this answer out of date? If it is, please let us know via a Comment