snowflake-cloud-data-platformregexp-like

In Snowflake Using regexp_like to search for data matching SSN pattern


I am trying to use regexp_like to search for an SSN pattern of 123-45-6789 OR XXXXXXXXX , I tried this but this is not working in Snowflake, this works in Oracle.

select * from customers where  regexp_like(PER_NUM, '^\d{3}-?\d{2}-?\d{4}$'); 

The table structure and sample data is as follows.

CREATE  or replace TABLE data_db.sch.customers (
  account_number NUMBER(38,0),
  first_name VARCHAR(16777216),
  last_name VARCHAR(16777216),
  email VARCHAR(16777216),
  per_num VARCHAR(16777216),
  SSN VARCHAR(16777216),
  nine_char VARCHAR(16777216)
);

INSERT INTO data_db.sch.customers (account_number, first_name, last_name, email, per_num,SSN, nine_char)
  VALUES
    (1589420, 'john', 'doe', 'john.doe@example.com', '232-76-1119', '232-76-1119','434689191'),
    (2834123, 'jane', 'doe', 'jane.doe@example.com', '434689191', NULL, '223788145'),
    (4829381, 'jim', 'doe', 'jim.doe@example.com',   '223788145', NULL, '678788235'),
    (9821802, 'susan', 'smith', 'susan.smith@example.com','123-73-3171', NULL,'789788235'),
    (8028387, 'bart', 'simpson', 'bart.barber@example.com','589-78-8239', NULL,'568788235'),
(8028367, 'Berry', 'Jane', 'ninecontinous@example.com','1234567890', NULL,'348788255');

Solution

  • \d should become \\d:

    select * 
    from customers 
    where regexp_like(PER_NUM, '^\\d{3}-?\\d{2}-?\\d{4}$'); 
    

    or using RLIKE:

    select * 
    from customers 
    where PER_NUM rlike '^\\d{3}-?\\d{2}-?\\d{4}$';