How to insert yes or no value into flag column based on regex query

I have a simple update that works great work analyzing a US based telephone number. It correctly inserts a Y in the phone_flag column based on the following query.

UPDATE database.table SET phone_flag = 'Y'
    WHERE id BETWEEN    1 and 100000
      AND (phone REGEXP '[0-9] {10}');

​x
 
UPDATE database.table SET phone_flag = 'Y'    WHERE id BETWEEN    1 and 100000      AND (phone REGEXP '[0-9] {10}');​

I want to insert a N value if it doesn’t match the expression, but I cannot figure out how to do it. Query I used.

UPDATE database.table
       SET phone_flag = CASE
             WHEN phone = REGEXP [0-9]{10} then 'Y'
             ELSE 'N' END
       WHERE id = '7'

 
UPDATE database.table       SET phone_flag = CASE             WHEN phone = REGEXP [0-9]{10} then 'Y'             ELSE 'N' END       WHERE id = '7'​

Anybody know how to fix it? Thanks.

Answer

Just:

UPDATE database.table 
SET phone_flag = CASE WHEN phone REGEXP '^[0-9]{10}$' then 'Y' ELSE 'N' END
WHERE id = 7

 
UPDATE database.table SET phone_flag = CASE WHEN phone REGEXP '^[0-9]{10}$' then 'Y' ELSE 'N' ENDWHERE id = 7​

Rationale: REGEXP is an operator, do not use = with it.

Also note that I modified the regex with ^ at the beginning and $ at the end: this forces phone to contain exactly 10 digits (not more, and no other character). You can revert that change if that’s not what you wanted.

Advertisement

Answer