SQL: Add some validation steps for phone number

Question

I have a fairly simple bit of SQL code that's truncating three fields into one called "phone_number":

Select pp.country_code_number||pp.area_code||pp.phone_number AS phone_number

What I'd like to do is:

1. Remove any non numeric values in the data (some values have "07111-245 123" for example which I'd like to convert to "07111245123"

2. If country code = "44" then only return the pp.phone_number AS phone_number ELSE return "+" and pp.country_code_number||pp.area_code||pp.phone_number AS phone_number

Answer 1

You can do it with CASE to check if the country code is 44 and the function REPLACE() (which works in most databases) to remove spaces and dashes:

select 
  replace(replace(case pp.country_code
    when '44' then pp.phone_number
    else '+' || pp.country_code_number || pp.area_code || pp.phone_number 
  end, ' ', ''), '-', '') AS phone_number
from phones pp

For Oracle and PostgreSQL you can use regexp_replace() to remove all non numeric chars
PostgreSQL:

select 
  case pp.country_code
    when '44' then regexp_replace(pp.phone_number, '[^0-9]+', '', 'g')
    else '+' || regexp_replace(pp.country_code_number || pp.area_code || pp.phone_number, '[^0-9]+', '', 'g') 
  end AS phone_number
from phones pp

Oracle:

select 
  case pp.country_code
    when '44' then regexp_replace(pp.phone_number, '[^0-9]+', '')
    else '+' || regexp_replace(pp.country_code_number || pp.area_code || pp.phone_number, '[^0-9]+', '') 
  end AS phone_number
from phones pp

Answer 2

Introduction

Considering I didn't know the database, I implemented this in Oracle, PostgresSql and also MySQL. These are the 3 databases that (in my experience) most commonly use || for concatenation (see: https://www.databasestar.com/sql-concatenate/ ).

What you need are two things:

• A string manipulation function to remove all non-numeric characters. In my example I used a stored procedure. Maybe a replace like Forpas example is good too (depending on your requirements ). In oracle you can also use REGEXP_REPLACE, see Oracle: Replacing non-numeric chars in a string .
• A Case expression or (decode in oracle also works) .

Example (Oracle SQL)

WITH pp AS (

  select '30' as country_code_number, '1234' as area_code, '1234567-555' as phone_number from dual
  UNION
  select '44' as country_code_number, '1234' as area_code, '1234567-555' as phone_number from dual
)


SELECT CONCAT(CASE
                WHEN pp.country_code_number = 44 THEN ' '
                ELSE concat('+', pp.country_code_number)
                END, REGEXP_REPLACE(CONCAT (pp.area_code, pp.phone_number), '[^0-9]+', '')) AS phone_number
FROM pp;

Example (Postgres)

select 

CONCAT(
CASE WHEN pp.country_code_number <> '44' then  concat('+',pp.country_code_number)
  ELSE ' ' 
  END
,

regexp_replace( concat (  pp.area_code , pp.phone_number ) , '[^0-9]+', '', 'g')
 )  as phone_number from

 pp;

Link: https://www.db-fiddle.com/f/p6ziyWyWCGXTCiyiYSSyS8/2

Example (MySQL)

  SELECT CONCAT(CASE
                  WHEN pp.country_code_number = 44 THEN " "
                  ELSE concat('+', pp.country_code_number)
              END, STRIP_NON_DIGIT(CONCAT (pp.area_code, pp.phone_number))) AS phone_number
FROM pp;

DDL

create table pp(

  country_code_number varchar(10),
  area_code varchar(10),
  phone_number varchar(20)
);

insert into pp values('30','210','123-456-789');
insert into pp values('44','210','123-456-789');

DROP FUNCTION IF EXISTS STRIP_NON_DIGIT;
DELIMITER $$
CREATE FUNCTION STRIP_NON_DIGIT(input VARCHAR(255))
   RETURNS VARCHAR(255)
BEGIN
   DECLARE output   VARCHAR(255) DEFAULT '';
   DECLARE iterator INT          DEFAULT 1;
   WHILE iterator < (LENGTH(input) + 1) DO
      IF SUBSTRING(input, iterator, 1) IN ( '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' ) THEN
         SET output = CONCAT(output, SUBSTRING(input, iterator, 1));
      END IF;
      SET iterator = iterator + 1;
   END WHILE;
   RETURN output;
END
$$

Link: https://www.db-fiddle.com/f/p6ziyWyWCGXTCiyiYSSyS8/1