Verify phone_number format using ORACLE, MYSQL, SQL SERVER , POSTGRESQL(SQL Interview Question)

In this post, we will explore a common SQL interview question:

how to validate the phone number format in the `phone_number` column using Oracle, MySQL, SQL Server, and PostgreSQL.
 
The valid phone number format is XXXX-XXX-XXX (e.g., 1234-567-890).

ORACLE - output

with cte as (

select '0111-222-333' as phone_number from dual  union all 

select '1111-222-333' as phone_number from dual   union all 

select '2111 222 333' as phone_number from dual   union all 

select '311-222-333' as phone_number from dual   union all 

select '411-2222-333' as phone_number from dual   union all 

select '511-222-3333' as phone_number from dual   union all 

select '61-2222-333' as phone_number from dual   union all 

select '7111-222-33' as phone_number from dual   union all 

select '8111-22-333' as phone_number from dual   union all 

select '9111-222-333' as phone_number from dual   union all 

select '1011222333' as phone_number from dual   union all 

select '1111-222-3p3' as phone_number from dual    

)

select phone_number,

case when regexp_like(phone_number,'^[0-9]{4}-[0-9]{3}-[0-9]{3}$')=1 then 'Valid' else 'InValid' end as result

from cte;

MYSQL-output

with cte as (

select '0111-222-333' as phone_number union all 

select '1111-222-333' as phone_number union all 

select '2111 222 333' as phone_number union all 

select '311-222-333' as phone_number union all 

select '411-2222-333' as phone_number union all 

select '511-222-3333' as phone_number union all 

select '61-2222-333' as phone_number union all 

select '7111-222-33' as phone_number union all 

select '8111-22-333' as phone_number union all 

select '9111-222-333' as phone_number union all 

select '1011222333' as phone_number union all 

select '1111-222-3p3' as phone_number  

)

select phone_number,

case when regexp_like(phone_number,'^[0-9]{4}-[0-9]{3}-[0-9]{3}$')=1 

then 'Valid' else 'InValid' end as result

from cte; 

SQL Server

with cte as (

select '0111-222-333' as phone_number union all 

select '1111-222-333' as phone_number union all 

select '2111 222 333' as phone_number union all 

select '311-222-333' as phone_number union all 

select '411-2222-333' as phone_number union all 

select '511-222-3333' as phone_number union all 

select '61-2222-333' as phone_number union all 

select '7111-222-33' as phone_number union all 

select '8111-22-333' as phone_number union all 

select '9111-222-333' as phone_number union all 

select '1011222333' as phone_number union all 

select '1111-222-3p3' as phone_number  

)

select phone_number ,

case when phone_number  like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]' then 'Valid' 

else 'InValid' end

from cte

POSTGRESQL

with cte as (

select '0111-222-333' as phone_number union all 

select '1111-222-333' as phone_number union all 

select '2111 222 333' as phone_number union all 

select '311-222-333' as phone_number union all 

select '411-2222-333' as phone_number union all 

select '511-222-3333' as phone_number union all 

select '61-2222-333' as phone_number union all 

select '7111-222-33' as phone_number union all 

select '8111-22-333' as phone_number union all 

select '9111-222-333' as phone_number union all 

select '1011222333' as phone_number union all 

select '1111-222-3p3' as phone_number  

)

select phone_number,

case when  phone_number  similar to '[0-9]{4}-[0-9]{3}-[0-9]{3}' then 'Valid' else 'InValid' end

from cte

--where phone_number  similar to '[0-9]{4}-[0-9]{3}-[0-9]{3}';

youtube link

video