CODEWITHSUNDEEP
sqloracle-database
user15746603
user15746603

Reputation:

Trimming telephone number

I have the below column

TEL
+440555123123
4+40555123123
0440555123123
00440555123123
440555123123
4455512443123
+055512443123
055512443123

I would like to remove the leading +|0|4 , with the below desired output. Also, if, after doing the replace, the number does not have a leading 0, then add it.

TEL
0555123123
0555123123
0555123123
0555123123
0555123123
055512443123
055512443123
055512443123

Upvotes: 0

Views: 58

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270483

I would explicitly list out the prefixes you want to get rid of -- even your question misses what they are. This seems like it might be tricky, so being explicit seems important.

You can use a lateral join to fix the prefix. Then a case expression to add the leading 0:

select t.*, 
       (case when tt.tel2 like '0%' then tt.tel2 else '0' || tt.tel2 end)
from t cross join lateral
     (select regexp_replace(tel, '^([+]44|4[+]4|044|44|[+])', '') as tel2 from dual) tt

Here is a db<>fiddle.

Upvotes: 2

MT0
MT0

Reputation: 168301

Assuming that your area code will not start with 0 or 4 and you are just removing the international prefix, you can use:

SELECT tel,
       '0' || LTRIM( tel, '+04' ) AS normalised_tel
FROM   TABLE_NAME;

Which, for the sample data:

CREATE TABLE table_name ( TEL ) AS
SELECT '+440555123123' FROM DUAL UNION ALL
SELECT '4+40555123123' FROM DUAL UNION ALL
SELECT '0440555123123' FROM DUAL UNION ALL
SELECT '00440555123123' FROM DUAL UNION ALL
SELECT '440555123123' FROM DUAL UNION ALL
SELECT '4455512443123' FROM DUAL UNION ALL
SELECT '+055512443123' FROM DUAL UNION ALL
SELECT '055512443123' FROM DUAL;

Outputs:

TEL NORMALISED_TEL
+440555123123 0555123123
4+40555123123 0555123123
0440555123123 0555123123
00440555123123 0555123123
440555123123 0555123123
4455512443123 055512443123
+055512443123 055512443123
055512443123 055512443123

db<>fiddle here

Upvotes: 1

Related Questions