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', 'Answer
\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}$';