CODEWITHSUNDEEP
sqlregexamazon-redshift
shantanuo
shantanuo

Reputation: 32296

using regular expressions in redshift

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}$'

Upvotes: 2

Views: 7566

Answers (2)

Michel Milezzi
Michel Milezzi

Reputation: 11105

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

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

Upvotes: 5

Bohemian
Bohemian

Reputation: 424983

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}$'

Upvotes: 2

Related Questions