SQL dealing with rubbish in a phone number field
- by DoctaJonez
Hello stackers!
I've got a wonderfully fun little SQL problem to solve today and thought I'd ask the community to see what solutions you come up with.
We've got a really cool email to text service that we use, you just need to send an email to [email protected] and it will send a text message to the desired phone number.
For example to send a text to 0790 0006006, you need to send an email to [email protected], pretty neat huh?
The problem is with the phone numbers in our database. Most of the phone numbers are fine, but some of them have "rubbish" mixed in with the phone number.
Take these wonderful examples of the rubbish you need to deal with (I've anonymised the phone numbers by placing zeroes in):
07800 000647(mobile)
07500 000189 USE 1ST
SEE NOTES
07900 000415 HO ONLY
try 1st 0770 0000694 then home
07500 000465 Cannot
Requirements
The solution needs to be in SQL (for MS SQL server).
So the challenge is as follows, we need to get the phone number without spaces, and without any of the rubbish seen in the samples.
For example:
This:
try 1st 0770 0000694 then home
Should become this:
07700000694
Anything without a phone number in it (e.g. "SEE NOTES") should be null.
