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
– Haleemur Ali, Commented Nov 9, 2017 at 16:56

Michel Milezzi · Accepted Answer · 2018-12-15 19:34:34Z

5

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

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

answered Nov 9, 2017 at 16:56

Michel Milezzi

11.4k3 gold badges24 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bohemian · Accepted Answer · 2017-11-09 16:57:04Z

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 9, 2017 at 16:57

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges