How to remove "rubbish" from column to stay only phone number in SQL?

Question

I have table in sql like below:

PHONE_NUMBER
--------
ppa:+77845678021@abc.gbc443.gbc356.7kffooopppp.deu  
tel:+77695002395    
....

As you can see I have in COL1 phone numbers with a lot of "rubbish" and I need to convert values in that column to have something like below (only phone number):

PHONE_NUMBER
--------
77845678021 
77695002395 
....

How can I do that in SQL ?

What BDMS is being used? And why have you dropped `+` from numbers in your column? — markalex, Apr 07 '23 at 09:48
markalex - could you present your solution in cole based on my example, please ? — dingaro, Apr 07 '23 at 09:48
markalex - it is HiveQL, because these data are from Hadoop, and exactly I need remove + — dingaro, Apr 07 '23 at 09:49
Careful! If you haven't done so read [Falsehoods programmers believe about phone numbers](https://github.com/google/libphonenumber/blob/master/FALSEHOODS.md). Especially if your application is intended for global use. — O. Jones, Apr 07 '23 at 10:21
yeras, could you present using my example data how to use replace ? — dingaro, Apr 07 '23 at 11:00
@dingaro, go delete this quesion yourself if it is no longer needed. — markalex, Apr 07 '23 at 15:33

markalex · Answer 1 · 2023-04-07T10:05:54.500

0

You can use regexp_substr. Something like this:

select regexp_substr(PHONE_NUMBER, '[0-9]{11,14}') PHONE_NUMBER
  from my_table

Demo for MySQL can be seen here.

This will extract first block of 11 to 14 digits. Since you haven't provided any specification on phone format, I'm guessing here.

EDIT: For Hive regexp_extract should be used instead:

select regexp_extract(PHONE_NUMBER, '[0-9]{11,14}') PHONE_NUMBER
  from my_table

edited Apr 07 '23 at 10:05

answered Apr 07 '23 at 09:55

markalex

8,623
2
7
32

markalex - I have ERROR like that using your code: KeyError: '11,14' – dingaro Apr 07 '23 at 10:00
It's strange, because as I can see Hive doesn't even have `regexp_substr`, it has `regexp_extract` instead. @dingaro – markalex Apr 07 '23 at 10:04
i changed to regexp_extract and I still have ERROR: KeyError: '11,14' – dingaro Apr 07 '23 at 10:12
@dingaro i am reading online that the regex looks very different between different versions of HiveQL, so I think that is impossible to answer at the moment. Unless people make a code for each possible version. – Johan Abdullah Holm Apr 07 '23 at 10:21
Johan, but do you have some example idea what can work ? – dingaro Apr 07 '23 at 10:22
@dingaro could make some examples, but since i dont know the version it could potentially take hours instead of minutes and a lot of back and forth. I think there is better use of my time. look at the documentation on regex it changes depending on the version of hive – Johan Abdullah Holm Apr 07 '23 at 10:25

How to remove "rubbish" from column to stay only phone number in SQL?

1 Answers1