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

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

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

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

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

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

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

Редактировать:
Для первоначального обновления, если вы используете 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)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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%^@#$^')  

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

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

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

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

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

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

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,

:)

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

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

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

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 символов ...