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

Я пишу утилиту импорта, которая использует телефонные номера в качестве уникального ключа в импорте.

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

Я пробовал использовать скрипт из этого поста:
T-SQL trim &nbsp (и другие не буквенно-цифровые символы)

но это не ускорило его.

есть ли быстрый способ удалить нечисловые символы? Что-то, что может хорошо работать, когда 10 000 до 100 000 записей должны быть сравнены.

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

обновление
Учитывая то, что люди ответили, Я думаю, что мне придется очистить поля, прежде чем запускать утилиту импорта.

чтобы ответить на вопрос о том, что я пишу утилиту импорта, это приложение C#. Я сравниваю BIGINT с BIGINT сейчас, без необходимости изменять данные БД, и я все еще беру хит производительности с очень небольшим набором данных (около 2000 записей).

может ли сравнение BIGINT с BIGINT быть замедление процесса?

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

15 59
sql performance sql-server optimization

15 ответов:

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

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

однако SQL не оптимизирован для такого рода операций, так как вы сказали, что пишете утилиту импорта, я бы сделал эти обновления в контексте самой утилиты импорта, а не в SQL. Это значительно лучше производительность мудрым. В чем вы пишете утилиту?

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

Edit:
Для первоначального обновления, если вы используете SQL Server 2005, вы можете попробовать функцию CLR. Вот быстрый, используя регулярное выражение. Не уверен, как будет сравниваться производительность, я никогда не использовал это сам, за исключением быстрого теста прямо сейчас.

using System;  
using System.Data;  
using System.Text.RegularExpressions;  
using System.Data.SqlClient;  
using System.Data.SqlTypes;  
using Microsoft.SqlServer.Server;  

public partial class UserDefinedFunctions  
{  
    [Microsoft.SqlServer.Server.SqlFunction]  
    public static SqlString StripNonNumeric(SqlString input)  
    {  
        Regex regEx = new Regex(@"\D");  
        return regEx.Replace(input.Value, "");  
    }  
};

после развертывания, чтобы обновление вы можете просто использовать:

UPDATE table SET phoneNumber = dbo.StripNonNumeric(phoneNumber)
15

Я видел это решение с кодом T-SQL и PATINDEX. Мне это нравится: -)

CREATE Function [fnRemoveNonNumericCharacters](@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @strText) > 0
    BEGIN
        SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
    END
    RETURN @strText
END
100

replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(string,'a',''),'b',''),'c',''),'d',''),'e',''),'f',''),'g',''),'h',''),'i',''),'j',''),'k',''),'l',''),'m',''),'n',''),'o',''),'p',''),'q',''),'r',''),'s',''),'t',''),'u',''),'v',''),'w',''),'x',''),'y',''),'z',''),'A',''),'B',''),'C',''),'D',''),'E',''),'F',''),'G',''),'H',''),'I',''),'J',''),'K',''),'L',''),'M',''),'N',''),'O',''),'P',''),'Q',''),'R',''),'S',''),'T',''),'U',''),'V',''),'W',''),'X',''),'Y',''),'Z','')*1 AS string,

:)

36

в случае, если вы не хотите создавать функцию, или вам нужен только один встроенный вызов в T-SQL, вы можете попробовать:

set @Phone = REPLACE(REPLACE(REPLACE(REPLACE(@Phone,'(',''),' ',''),'-',''),')','')

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

15

простая функция:

CREATE FUNCTION [dbo].[RemoveAlphaCharacters](@InputString VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
  WHILE PATINDEX('%[^0-9]%',@InputString)>0
        SET @InputString = STUFF(@InputString,PATINDEX('%[^0-9]%',@InputString),1,'')     
  RETURN @InputString
END

GO
9
create function dbo.RemoveNonNumericChar(@str varchar(500))  
returns varchar(500)  
begin  
declare @startingIndex int  
set @startingIndex=0  
while 1=1  
begin  
    set @startingIndex= patindex('%[^0-9]%',@str)  
    if @startingIndex <> 0  
    begin  
        set @str = replace(@str,substring(@str,@startingIndex,1),'')  
    end  
    else    break;   
end  
return @str  
end

go  

select dbo.RemoveNonNumericChar('aisdfhoiqwei352345234@#$%^$@345345%^@#$^')
6

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

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

1

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

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

Если вы знаете, что подавляющее большинство ваших записей имеют нечисловые символы, это может не помочь.

1

Я знаю, что это поздно для игры, но вот функция, которую я создал для 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
1

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

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

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

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

1

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

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

дисплей: (555) 555-5555 ext 555

вход: 10 цифр или более цифр, встроенных в любую строку. (Замена регулярных выражений удаляет все нечисловые символы)

0

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

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

0

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

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

0

Ищу супер простое решение:

SUBSTRING([Phone], CHARINDEX('(', [Phone], 1)+1, 3)
       + SUBSTRING([Phone], CHARINDEX(')', [Phone], 1)+1, 3)
       + SUBSTRING([Phone], CHARINDEX('-', [Phone], 1)+1, 4) AS Phone
0

Я бы использовал встроенную функцию с точки зрения производительности, см. ниже: обратите внимание, что символы, такие как '+','-' и т. д. не будут удалены

CREATE FUNCTION [dbo].[UDF_RemoveNumericStringsFromString]
 (
 @str varchar(100)
 )
 RETURNS TABLE AS RETURN
 WITH Tally (n) as 
  (
  -- 100 rows
   SELECT TOP (Len(@Str)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
   FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
   CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
  )

  SELECT OutStr =  STUFF(
       (SELECT SUBSTRING(@Str, n,1) st
        FROM Tally
        WHERE ISNUMERIC(SUBSTRING(@Str, n,1)) = 1
        FOR XML PATH(''),type).value('.', 'varchar(100)'),1,0,'')
  GO

  /*Use it*/
  SELECT OutStr
  FROM dbo.UDF_RemoveNumericStringsFromString('fjkfhk759734977fwe9794t23')
  /*Result set
   759734977979423 */

вы можете определить его с более чем 100 символов...

0