Самый быстрый способ удалить нечисловые символы из VARCHAR в SQL Server

Я рекомендовал бы осуществить строгий формат для номеров телефона в базе данных. Я использую следующий формат. (Принятие американских номеров телефона)

База данных: 5555555555x555

Дисплей: (555) 555-5555 расширений 555

Вход: 10 цифр или больше цифр встраиваются в любую строку. (Замена Regex удаляет все нечисловые символы)

Тысячи записей против тысяч записей обычно не являются проблемой. Я использовал SSIS для импорта миллионов записей с дедупликацией как это.

я очистил бы базу данных, чтобы удалить нечисловые символы во-первых и не пустить их.

"Хотя я не могу изолировать SQL как источник проблемы больше, я все еще чувствую, что это".

Разжигают SQL Profiler и смотрят. Возьмите получающиеся запросы и проверьте их планы выполнения, чтобы удостовериться, что индекс используется.

Работа с varchars является существенно медленной и неэффективной по сравнению с работой с численными данными по очевидным причинам. Функции, с которыми Вы связываетесь в исходном сообщении, действительно будут довольно медленными, поскольку они циклично выполняются через каждый символ в строке, чтобы определить, является ли это числом. Сделайте это для тысяч записей и процесса обязано быть медленным. Это - идеальное задание для Регулярных выражений, но они исходно не поддерживаются в SQL Server. Можно добавить поддержку с помощью функции CLR, но трудно сказать, насколько медленный это будет, не пробуя его, я определенно ожидал бы, что он будет значительно быстрее, чем цикличное выполнение через каждый символ каждого номера телефона, однако!

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

Так или иначе, хотя, Вы собираетесь иметь большую проблему относительно дополнительного форматирования. Даже если Ваши числа, как будут гарантировать, будут только североамериканскими в источнике, некоторые люди поместят 1 перед квалифицированным номером телефона полностью кода зоны, и другие не будут, который вызовет потенциал для многократных въездов того же номера телефона. Кроме того, в зависимости от того, что представляют Ваши данные, некоторые люди будут использовать свой домашний телефон, который мог бы иметь несколько человек, живущих там, таким образом, ограничение на уникальность данных на него только позволит одному участнику базы данных на домашнее хозяйство. Некоторые использовали бы их число работы и имели бы ту же проблему, и некоторые будут или не включать расширение, которое вызовет искусственный потенциал уникальности снова.

Все это может или не может повлиять на Вас, в зависимости от Ваших конкретных данных и использований, но важно иметь в виду!

можно ли удалить их в ночном процессе, храня их в отдельном поле, затем сделать ли обновление на измененных записях прямо перед выполнением процесса?

Или на вставлении/обновлении, сохраните "числовой" формат к ссылке позже. Триггер был бы простым способом сделать это.

Я попробовал бы функцию CLR Scott сначала, но добавил бы оператор Where для сокращения количества обновленных записей.

UPDATE table SET phoneNumber = dbo.StripNonNumeric(phoneNumber) 
WHERE phonenumber like '%[^0-9]%'

, Если Вы знаете, что у значительного большинства Ваших записей есть нечисловые символы, которым оно не могло бы помочь все же.

Я знаю, что это поздно, но вот функция, которую я создал для T-SQL, которая быстро удаляет нечисловые символы. Примечательно, что у меня есть схема "String", в которую я поместил утилитные функции для строк...

CREATE FUNCTION String.ComparablePhone( @string nvarchar(32) ) RETURNS bigint AS
BEGIN
    DECLARE @out bigint;

-- 1. table of unique characters to be kept
    DECLARE @keepers table ( chr nchar(1) not null primary key );
    INSERT INTO @keepers ( chr ) VALUES (N'0'),(N'1'),(N'2'),(N'3'),(N'4'),(N'5'),(N'6'),(N'7'),(N'8'),(N'9');

-- 2. Identify the characters in the string to remove
    WITH found ( id, position ) AS
    (
        SELECT 
            ROW_NUMBER() OVER (ORDER BY (n1+n10) DESC), -- since we are using stuff, for the position to continue to be accurate, start from the greatest position and work towards the smallest
            (n1+n10)
        FROM 
            (SELECT 0 AS n1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS d1,
            (SELECT 0 AS n10 UNION SELECT 10 UNION SELECT 20 UNION SELECT 30) AS d10
        WHERE
            (n1+n10) BETWEEN 1 AND len(@string)
            AND substring(@string, (n1+n10), 1) NOT IN (SELECT chr FROM @keepers)
    )
-- 3. Use stuff to snuff out the identified characters
    SELECT 
        @string = stuff( @string, position, 1, '' )
    FROM 
        found
    ORDER BY
        id ASC; -- important to process the removals in order, see ROW_NUMBER() above

-- 4. Try and convert the results to a bigint   
    IF len(@string) = 0
        RETURN NULL; -- an empty string converts to 0

    RETURN convert(bigint,@string); 
END

Затем, чтобы использовать ее для сравнения для вставки, что-то вроде этого;

INSERT INTO Contacts ( phone, first_name, last_name )
SELECT i.phone, i.first_name, i.last_name
FROM Imported AS i
LEFT JOIN Contacts AS c ON String.ComparablePhone(c.phone) = String.ComparablePhone(i.phone)
WHERE c.phone IS NULL -- Exclude those that already exist