Regexp pattern for special characters

Question

I have the data in the format like Input:

Code_1
FAB
?
USP BEN,
.
-
,

Output:

Code_1
FAB
IP BEN,

I need to exclude only the value which have length as 1 and and are special characters

I am using (regexp_like(code_1,'^[^<>{}"/|;:.,~!?@#$%^=&*\]\\()\[¿§«»ω⊙¤°℃℉€¥£¢¡®©0-9_+]')) AND LENGTH(CODE_1)>=1

I have also tried REGEXP_LIKE(CODE_1,'[A-Za-z0-9]')

So would you say that these 'special characters' are any but `[A-Za-z]`? — JvdV, Nov 25 '22 at 10:48
I have to basically exclude these special characters from my column. Rest should be as it is irrespective of alphabets or numbers — Nidhi Dugar, Nov 25 '22 at 10:50
In that case you might have mistakenly added `0-9` to the character class in your 1st attempt? — JvdV, Nov 25 '22 at 10:51

score 1 · Answer 1 · answered Nov 25 '22 at 15:50

Based on your requirements which I understand are you want data that is not single character AND non-alpha numeric (at the same time), this should do it for you.

The 'WITH' clause just sets up test data in this case and can be thought of like a temp table here. It is a great way to help people help you by setting up test data. Always include data you don't expect! The actual query starts below and selects data that uses grouping to get the data that is NOT a group of non-alpha numeric with a length of one. It uses a POSIX shortcut of [:alnum:] to indicate [A-Za-z0-9].

Note your requirements will allow multiple non-alnum characters to be selected as is indicated by the test data.

WITH tbl(DATA) AS (
  SELECT 'FAB' FROM dual UNION ALL
  SELECT '?' FROM dual UNION ALL
  SELECT 'USP BEN,' FROM dual UNION ALL
  SELECT '.' FROM dual UNION ALL
  SELECT '-' FROM dual UNION ALL
  SELECT '----' FROM dual UNION ALL
  SELECT ',' FROM dual UNION ALL
  SELECT 'A' FROM dual UNION ALL
  SELECT 'b' FROM dual UNION ALL
  SELECT '5' FROM dual
)
SELECT DATA
FROM tbl
WHERE NOT (REGEXP_LIKE(DATA, '[^[:alnum:]]')
AND   LENGTH(DATA) = 1);

DATA      
----------
FAB       
USP BEN,  
----      
A         
b         
5         

6 rows selected.

Regexp pattern for special characters

1 Answers1