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