CODEWITHSUNDEEP
sqlregexoracle-database
Jake
Jake

Reputation: 604

Oracle - RegEx pattern to return records that have non-alphanumeric characters NOT including spaces

Here is a sample column:

+---------------+
|     NAME      |
+---------------+
| Jim Jo'nes    |
| John $mith    |
| Leroy Jenkins |
| Tom & Jerry   |
+---------------+

I need to write a RegEx pattern that returns fields that include non-alphanumeric characters NOT including spaces. This is a name field that can contain multiple names separated by spaces.

My expected result set is this:

Jim Jo'nes
John $mith
Tom & Jerry

Upvotes: 0

Views: 3446

Answers (2)

Patrick Bacon
Patrick Bacon

Reputation: 4640

Use a REGEXP_LIKE Condition with a Negated Character Set

I would first turn off the use of '&' to identify substition variables:

set define off;

Next, I would just identify all characters you are not looking for:

a-z, A-Z, 0-9, \s (the escape version of a space character)

I then create a negated character set:

[^a-zA-Z0-9 ]

Here is my resulting solution:

SCOTT@tst>WITH names AS (
  2      SELECT
  3          'Jim Jo''nes' name
  4      FROM
  5          dual
  6      UNION ALL
  7      SELECT
  8          'John $mith' name
  9      FROM
 10          dual
 11      UNION ALL
 12      SELECT
 13          'Leroy Jenkin' name
 14      FROM
 15          dual
 16      UNION ALL
 17      SELECT
 18          'Tom & Jerry' name
 19      FROM
 20          dual
 21  ) SELECT
 22      *
 23  FROM
 24      names
 25  WHERE
 26          1 = 1
 27      AND
 28          REGEXP_LIKE ( names.name,'[^a-zA-Z0-9 ]' );


name         
---------- 
Jim Jo'nes   
John $mith   
Tom & Jerry

Upvotes: 0

Dmitry Egorov
Dmitry Egorov

Reputation: 9650

Use the [^ ... ] operator (non-matching character list) applied to alphanumeric ([:alnum:]) and space ([:space:]) character classes:

[^[:alnum:][:space:]]

Demo: https://regex101.com/r/VOzqFn/1

Upvotes: 1

Related Questions