SQL processing garbage in phone number field

Question

I have a remarkably funny little SQL problem to solve today, and I thought I'd ask the community to see what solutions you came up with.

We have a really cool email for the text service we use, you just need to send an email to [e-mail] and it will send a text message to the desired phone number.

For example, to send text to number 0790 0006006, you need to send an email to number [e-mail] , pretty neat huh?

Problem with phone numbers in our database. Most phone numbers are fine, but some have junk mixed with the phone number.

Take these great examples of garbage that you need to deal with (I anonymized phone numbers by setting zeros):

07800 000647 (mobile)
07500 000189 USE 1ST SEE
NOTES
07900 000415 HO ONLY
try 1st 0770 0000694, then go home
07500 000465 Cannot

Requirements

The solution should be in SQL (for MS SQL server).

So, the task is this: we need to get a phone number without spaces and without any garbage seen in the samples.

For instance:

It:

try 1st 0770 0000694 then home

Should be as follows:

07700000694

, (, ". " ) .

UPDATE:

! , , , SQL , . , .

, - , .

Answer 1

, yopur "07", - 12 , - :

DECLARE @Number varchar(50)

--SET @Number='07800 000647(mobile)'
--SET @Number='07500 000189 USE 1ST'
--SET @Number='SEE NOTES'
--SET @Number='07900 000415 HO ONLY'
--SET @Number='try 1st 0770 0000694 then home'
SET @Number='07500 000465 Cannot '



SELECT REPLACE(SUBSTRING(@Number, case when CHARINDEX ('07',@Number ) =0 then Null 
else CHARINDEX ('07',@Number )end , 12),' ','')

, "07", , 0 ( " " ), Null. 12 . , ...

Answer 2

. , , , . . , , .

Answer 3

, , ( SQL , ). - , "1-" "2-", , REPLACE('1ST','') -.

, , . , , , .

, , , SQL Server. , , SQL Server. Google " SQL Server" .

Answer 4

DECLARE @test varchar (100)
DECLARE @result varchar (100)
SET @test = '07800 000647 ()'

SET @result = ''

@result = @result + CASE LIKE '[0-9]' THEN number ELSE '' END FROM
(
  SELECT SUBSTRING (@test, number, 1) AS number FROM
  (
    SELECT NUMBER FROM Master..spt_values ​​WHERE type = 'p' 1 len (@test)
  ) AS temp
) AS temp
SELECT @result

MusicGenesis, - 1- 2- .

Answer 5

, : ( )

CREATE FUNCTION fnRipMsisdn(@STRING VARCHAR(28)) RETURNS VARCHAR(28) AS
BEGIN
DECLARE @I INT, @RESULT VARCHAR(28), @CHAR CHAR, @CONCURRENT_ALPHA INT
SET @I = 0
SET @RESULT = ''
SET @CONCURRENT_ALPHA = 0
SET @STRING = REPLACE(@STRING, ' ', '') --replace chars that can delimit an msisdn

WHILE @I < LEN(@STRING) BEGIN
    IF LEN(@RESULT) >= 13 --MAX LEN
        BREAK
    SET @I = @I + 1
    SET @CHAR = SUBSTRING(@STRING, @I, 1)
    IF @CHAR LIKE '[0-9]' AND @CONCURRENT_ALPHA < 1 BEGIN
        SET @CONCURRENT_ALPHA = 0
        SET @RESULT = @RESULT + @CHAR
    END ELSE BEGIN
        SET @CONCURRENT_ALPHA = @CONCURRENT_ALPHA + 1
        IF LEN(@RESULT) <= 9 BEGIN --MIN LEN
            SET @RESULT = ''
        END
    END
END
RETURN CASE WHEN @RESULT = '' THEN NULL ELSE @RESULT END
END

select dbo.fnRipMsisdn('07800 000647(mobile)')
select dbo.fnRipMsisdn('07500 000189 USE 1ST')
select dbo.fnRipMsisdn('SEE NOTES')
select dbo.fnRipMsisdn('07900 000415 HO ONLY')
select dbo.fnRipMsisdn('try 1st 0770 0000694 then home')
select dbo.fnRipMsisdn('07500 000465 Cannot')

07800000647
07500000189
NULL
07900000415
07700000694
07500000465

Answer 6

, , :

SELECT 
CASE WHEN ISNUMERIC(SUBSTRING(REPLACE(MobilePhone, ' ', ''), 1, 11)) = 1 
THEN SUBSTRING(REPLACE(MobilePhone, ' ', ''), 1, 11) + '@emailservice.com' 
ELSE NULL END AS EmailToTextAddress
FROM Contacts

.

It is also assumed that the phone number (without spaces) is 11 characters long, which allows me to deal with numerical captors that are not part of the phone number (as in MusiGenesis's answer).