using regular expressions in redshift

Question

This query works in mysql but I am not sure how to write the same query in redshift / postgresql.

update customer_Details set
customer_No = NULL
WHERE customer_No NOT REGEXP '^[[:digit:]]{12}$'

replace `NOT REGEXP` with `!~`. You can shorten the regex pattern further to `^\d{12}$` — Haleemur Ali, Nov 09 '17 at 16:56

Michel Milezzi · Accepted Answer · 2018-12-15T19:34:34.560

5

You need to use !~ operator. Something like this should work:

UPDATE
    customer_details 
SET 
    customer_no = NULL 
WHERE 
    customer_No !~ '^[[:digit:]]{12}$';

edited Dec 15 '18 at 19:34

answered Nov 09 '17 at 16:56

Michel Milezzi

score 2 · Answer 2 · answered Nov 09 '17 at 16:57

2

Redshift is basically a fork of postgres 8.3, and it uses postgres's regex syntax:

update customer_Details set
customer_No = NULL
WHERE customer_No ! ~ '^[0-9]{12}$'

answered Nov 09 '17 at 16:57

Bohemian

2 Answers2