Using SQL, how can I get the number of unique digits for every phone number?

Question

I have a table called phone_numbers which can be in length from 10-12 digits. I am using Snowflake UI and would like to get the the number of unique values for every phone number. For instance, 9999999999 should have a value of 1. 1234567890 should have a value of 10.

Thanks in advance!

I have tried some regex functions like the following.

SELECT phone_number,
       LENGTH(phone_number) -
         LENGTH(REGEXP_REPLACE(phone_number, '[0-9]', '')) AS unique_digit_count
FROM phone_numbers

Answer 1

You can add commas between all the characters using regexp_replace and then use split_to_table, and then count the number of distinct values per phone number.

with my_data as (
 select 1 as id, 9999999999 as phone_number union all
 select 2, 1234567890 union all
 select 3, 333444455555
)
select 
 d.id, 
 d.phone_number, 
 count(distinct y.value) as distinct_digits
from my_data d, 
 table(split_to_table(regexp_replace(d.phone_number, '.', ',\\0', 2), ',')) y
group by 1,2

Output:

ID	PHONE_NUMBER	DISTINCT_DIGITS
1	9999999999	1
2	1234567890	10
3	333444455555	3