SQL, имеющий дело с мусором в поле номера телефона

Решение, которое я придумал до сих пор, выглядит следующим образом:

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

Однако это не касается мусора в начале номера телефона.

Также предполагается, что номер телефона (без пробелов) состоит из 11 символов, что позволяет мне иметь дело с числовыми символами, которые не являются частью номера телефона (как в ответе MusiGenesis).

Похоже, вы могли бы искать длинные непрерывные строки чисел: (быстро и грязно)

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

Судя по вашим образцам, похоже, что по большей части вам просто нужно удалить все нечисловые символы и пробелы из строки (я не вспомните функцию SQL для этого, но она тривиальна). Единственное исключение составляют такие вещи, как «1st» или «2nd», и вы можете избавиться от них, прежде чем удалять нечисловые символы с помощью группы REPLACE ('1ST', '') -type заявления.

В ваших данных может быть гораздо больше странных ситуаций, когда люди включают реальные цифры, которые на самом деле не являются частью их номера телефона. Я не уверен, как вы все это находите и исправляете, кроме как просто разбираясь с ними всякий раз, когда вы их обнаруживаете.

Несомненно, существует много сторонних компонентов, которые обрабатывают разбор телефонных номеров, но я не знаю, можно ли их использовать непосредственно из SQL Server. Возможно, некоторые из них могут, в зависимости от вашей версии SQL Server. Поиск в Google по запросу «анализировать телефонные номера в SQL Server» дает множество вариантов.

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

SET @result = ''
SELECT
@ result = @ result + CASE WHEN number LIKE '[0-9]' THEN number ELSE '' END FROM
{{1 }} (
ВЫБРАТЬ ПОДСТРОКУ (@ 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-го отдельно.

Лучше всего исправить данные. Если вы не можете исправить данные, введите новое вычисляемое поле, в котором удаляются ненужные символы. В любом случае начните прямо сейчас, чтобы установить элементы управления вводом данных в это поле вашего приложения. Честно говоря, вы не хотите тратить вычислительную мощность впустую, выполняйте такие манипуляции с данными с каждым запросом, сделайте это один раз, когда данные вводятся, и покончите с ними.