How to fetch specific data using regular expression in postgres sql

Question

I need to fetch the values of a column if it contains special characters other than alphanumeric.

Ex: select name from data;

name

ACD12
A12DD
A_C12
A@CD
AB_M1
123AB

I need to write a regular expression to fetch the following data from the above table:

A_C12
A@CD
AB_M1

Craig Ringer · Accepted Answer · 2012-11-05 10:48:04Z

up vote 3 down vote accepted

You need an expression that says "Any string that contains at least one non-alpha-numeric character":

SELECT name FROM table1 WHERE name ~ '[^[:alnum:]]'

I haven't used \W because it's equivalent to [^[:alnum:]_] and you don't want the underscore. See the PostgreSQL docs on regular expressions.

answered Nov 5 '12 at 10:48

Craig Ringer
74.8k681139

Thanks, its working fine – Ram Ch Nov 5 '12 at 11:01

add a comment |

1 Answer 1