CODEWITHSUNDEEP
sqlregexdatabaseoracle-database
Rajiv A
Rajiv A

Reputation: 943

Oracle regular expression to pull rows from a table

I have to pull rows from a table where the column value contains anything else apart from alphabets, numbers, hyphen, underscore, space, and comma. It should not pull rows if it has only the above mentioned characters. Thanks for the help.

I was trying something regexp_like but 'm not getting the desired result.

REGEXP_LIKE(col, '[^[:alnum:]]|(^-)|(^,)|[^[:space:]]|(^_)')

For example:

TD-6 TTY-60X3APTNO DAY  => should not retrieve
TY-2YYTY 20TRH TY L/W   => Should retrieve
TIRREG CL3- HARDWARE &  => Should retrieve

Upvotes: 1

Views: 72

Answers (2)

Paul Griffin
Paul Griffin

Reputation: 2436

Using Oracle's regex conventions:

REGEXP_LIKE(col, '[^[:alnum:] _,-]')

Here's a SQLFilddle

The way the brackets work is that anything inside the bracket is matched, or if you lead the items in the bracket with a caret (^), anything EXCEPT what is in the brackets is matched.

Upvotes: 5

David Faber
David Faber

Reputation: 12485

You want everything that is not alphanumeric, or an underscore, or a comma, or a hyphen? Is that correct? Then you want something like this:

REGEXP_LIKE(col, '[^A-Za-z0-9_ ,-]')

Test:

WITH d1 AS (
    SELECT 'TD-6 TTY-60X3APTNO DAY' AS col FROM dual
     UNION ALL
    SELECT 'TY-2YYTY 20TRH TY L/W' FROM dual
     UNION ALL
    SELECT 'TIRREG CL3- HARDWARE &' FROM dual
)
SELECT col FROM d1
 WHERE REGEXP_LIKE(col, '[^A-Za-z0-9_ ,-]')

Returns:

TY-2YYTY 20TRH TY L/W
TIRREG CL3- HARDWARE &

Upvotes: 3

Related Questions