Regex Based SQL Interview Question | Postgres Regular Expressions | SQL for Analytics

[@srinivasulum414]

Thanks Ankit, every time you come up with different logics.

[@SnapMathShorts]

I approach it like this :
with cte as (
select *, right(num , length(num)- position ('-' in num)) as phone_without_ext
from phone_numbers),
cte2 as (
select phone_without_ext, regexp_split_to_table(phone_without_ext,'') as number
from cte )
select phone_without_ext as phone_number
from cte2
group by 1
having length(phone_without_ext) = count(distinct number)

[@GiriPrasath.D]

From your videos only. Im learning many new sql function and how to solve it in creative way.

[@torrentdownloada]

with cte as (select *,substring(num,CHARINDEX('-',num,0)+1,len(num)+1) ph
from phone_numbers)
,cte1 as (
select num,ph,SUBSTRING(ph,1,1) a,1 as b from cte
union all
select num,ph,SUBSTRING(ph,b+1,1) a,b+1 from cte1
where len(ph)>b
)
select ph,count(distinct(a)),len(ph) from cte1 group by ph having count(distinct(a))=len(ph)

[@ravidubey122]

Thanks Ankit, I always learn new way to handle these type of unique questions and it's approach. really appreciated.

[@ankitbansal6]

Glad to hear that😊

[@narutomaverick]

Great solution

[@sravankumar1767]

Superb explanation Ankit 👌 👏 👍

[@souravdas-kt7gg]

nice questiion

[@aaliyarahman5526]

Outstanding explanation 👏

[@ankitbansal6]

Glad you think so!

[@tatipamulaashok5721]

Very nice

[@Manju-gf2yr]

For postgreSQL v14 and above, we can use '-1' as position for split_part function to avoid using case statement.
Full query:
with ct as
(SELECT * ,
split_part(num, '-', -1) new_num
FROM phone_numbers)
select num, new_num, count(*), count(distinct digits) from
(
select *, regexp_split_to_table(new_num, '') as digits
from ct) ft
group by num, new_num
having count(*) = count(distinct digits)

[@ShubhamSaha-m3h]

In Sql Server.
---------------------------------
with cte as(
select *,substring(num,CHARINDEX('-',num)+1,len(num)) as new_num
from phone_numbers)
select new_num
from cte
cross apply STRING_SPLIT(CAST(CAST(CAST(new_num AS NVARCHAR) AS VARBINARY) AS VARCHAR), CHAR(0))
group by new_num
having count(distinct value)=count(value)

[@ankitbansal6]

Good one 👍

[@varunas9784]

Brilliant approach!
Could you please explain me the specifics as to what is happening in our code, especially below part:
CAST(CAST(CAST(new_num AS NVARCHAR) AS VARBINARY) AS VARCHAR)
i.e, what is happening behind the scenes while converting/ casting it as varchar, varbinary, nvarchar??
Thanks in advance!

[@sahilummat8555]

@@ankitbansal6 Can you please explain this in SQL server as well

[@manideepu1343]

Bro please do it in MYSQL, postgres dont use much and their functionalitis also not present in MSSQL as i know. No use to do in Postgres.😊

[@ishanshubham8355]

For MYSQL
with recursive cte as (
select
substring_index(num,"-",-1) as num,
length(substring_index(num,"-",-1)) as cnt
from phone_numbers
union all
select num,cnt-1
from cte
where cnt >1),
cte2 as (
select num,numbers,count(numbers) over(partition by num,numbers) as cnt
from(
select num,
substring(num,cnt,1) as numbers
from cte )m)
select num
from cte2
group by num
having max(cnt)=1

[@manideepu1343]

@@ishanshubham8355 thank you

[@ShubhamSingh-ri5en]

I believe ; is called a semicolon

[@vidaan95]

Solution in Snowflake:
WITH split_nbr AS (
SELECT
phone_nbr,
CASE
WHEN split_part(phone_nbr, '-', 2) '' THEN split_part(phone_nbr, '-', 2)
ELSE phone_nbr
END AS only_nbr,
REGEXP_SUBSTR_ALL(only_nbr, '[^\s-]') AS array_nbr --splits the numbers to individual characters
FROM phone_nbr
),
flattened_data AS (
SELECT
only_nbr,
array_nbr,
value::NUMBER AS value
FROM split_nbr s,
LATERAL FLATTEN (input => s.array_nbr)
)
SELECT
only_nbr,
COUNT(value) AS num_digits,
COUNT(DISTINCT value) AS dist_digits
FROM flattened_data
GROUP BY only_nbr
HAVING dist_digits = num_digits;

[@abhishekbhatt3030]

Ankit Sir,
SUBSTRING(REVERSE(num), 1,10)
reverse and extract 10 digits. Please tell what you think of this.
We only want digits only, this will serve the purpose

[@Manifestion_kannada]

ANY ONE SOLVED IN SQL DEVELOPER ORACLE

[@mithunmahato309]

There is no job

[@KrishnaVermaAnalytics]

solution in MySQL:
SELECT cleaned_number
FROM (
SELECT
CASE
WHEN num LIKE '+%-%' THEN RIGHT(REPLACE(num, '-', ''), 10)
WHEN num LIKE '+%' THEN RIGHT(num, 10)
ELSE num
END AS cleaned_number,
num
FROM phone_numbers
) AS subquery
WHERE LENGTH(cleaned_number) = 10
AND cleaned_number REGEXP '^[0-9]+$'
AND cleaned_number REGEXP '^(?!.*(.).*\\1)[0-9]{10}$';