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