Вопрос по sql, optimization, sql-server, performance – Самый быстрый способ удалить нечисловые символы из VARCHAR в SQL Server

59

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

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

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

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

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

Что бы ни было сделано, нужно выполнитьfast.

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

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

Может ли сравнение BIGINT с BIGINT замедлить ход событий?

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

Ваш Ответ

15   ответов
6
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('[email protected]#$%^[email protected]%^@#$^')  
102

Я видел это решение с кодом 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
16

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

Для обновления существующих записей я бы просто использовал 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)
0

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

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

-1

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

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

Дисплей: (555) 555-5555 доб 555

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

9

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

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
36

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,

:)

Вы забыли () - # и т. Д.
* CAST (1 как bigint)
1

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

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

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

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

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

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

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

16

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

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

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

0

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

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

1

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

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

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

0

Я бы использовал встроенную функцию с точки зрения производительности, см. Ниже: Note that symbols like '+','-' etc will not be removed

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

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

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

Похожие вопросы