SQL Server에서 VARCHAR에서 숫자가 아닌 문자를 가장 빨리 제거하는 방법

SQL Server에서 VARCHAR에서 숫자가 아닌 문자를 가장 빨리 제거하는 방법

저는 가져오기 내에서 전화번호를 고유 키로 사용하는 가져오기 유틸리티를 작성하고 있습니다.

제 DB에 전화번호가 존재하지 않는지 확인해야 합니다.문제는 DB의 전화번호에 대시, 괄호 등이 포함될 수 있다는 점입니다.이러한 것들을 제거하는 기능을 작성했는데, 문제는 이것이 느리고 DB에 있는 수천 개의 레코드와 한 번에 가져올 수천 개의 레코드로 인해 이 프로세스가 용납할 수 없을 정도로 느릴 수 있다는 것입니다.저는 이미 전화번호란을 색인으로 만들었습니다.

저는 이 게시물의 스크립트를 사용해 보았습니다.
T-SQL 트림 &nbsp(및 기타 영숫자가 아닌 문자)

하지만 그것은 속도를 높이지 못했습니다.

숫자가 아닌 문자를 더 빨리 제거할 수 있는 방법이 있습니까?10,000개에서 10,000개의 레코드를 비교해야 할 때 좋은 성과를 낼 수 있는 것.

어떤 작업이든 신속하게 수행해야 합니다.

사람들의 반응을 볼 때 수입 유틸리티를 실행하기 전에 필드를 청소해야 할 것 같습니다.

Import 유틸리티를 무엇에 쓰고 있는지에 대한 질문에 답하자면, 그것은 C# 앱입니다.저는 지금 DB 데이터를 변경할 필요 없이 BIGINT와 BIGINT를 비교하고 있으며, 여전히 매우 작은 데이터 세트(약 2000개의 레코드)로 성능에 영향을 미치고 있습니다.

BIGINT와 BIGINT를 비교하는 것이 상황을 지연시킬 수 있습니까?

앱의 코드 측면을 최대한 최적화했습니다(정규 제거, 불필요한 DB 호출 제거).더 이상 SQL을 문제의 근원으로 분리할 수는 없지만, 여전히 그런 것처럼 느껴집니다.

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

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,

:)

함수를 만들고 싶지 않거나 T-SQL에서 하나의 인라인 호출만 필요한 경우 다음을 시도할 수 있습니다.

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

물론 이것은 전화 번호 형식을 제거하는 것에만 한정되며 문자열 기능에서 모든 특수 문자를 제거하는 일반적인 것은 아닙니다.

오해할 수도 있지만 데이터베이스의 현재 데이터에 대한 문자열을 하나에서 제거하고 가져올 때마다 새 데이터 집합을 하나에서 제거하는 데이터 집합이 두 개 있습니다.

기존 기록을 업데이트할 때는 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)

간단한 기능:

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

SQL Server 2017부터는 기본 기능을 사용할 수 있습니다.

모든 하나의 문자로 해당 ▁that▁a▁with▁character▁out▁if▁▁strip▁and▁first니▁then제▁theto▁to▁of▁known▁all▁character▁simply▁bad▁use▁(▁following▁bad). 먼저 모든 잘못된 문자를 알려진 잘못된 단일 문자로 변환한 다음 특정 문자를 제거합니다.REPLACE)

DECLARE @BadCharacters VARCHAR(256) = 'abcdefghijklmnopqrstuvwxyz';
        
SELECT REPLACE(
                TRANSLATE(YourColumn, 
                          @BadCharacters, 
                          REPLICATE(LEFT(@BadCharacters,1),LEN(@BadCharacters))),
                LEFT(@BadCharacters,1),
                '')
FROM   @YourTable

만약 가능한 다 할 수 , "고장" "고장" "고장"이라는 단어를 할 수 .TRANSLATE

DECLARE @CharactersToKeep VARCHAR(30) = '0123456789', 
        @ExampleBadCharacter CHAR(1) = CHAR(26); 
        
SELECT REPLACE(TRANSLATE(YourColumn, bad_chars, REPLICATE(@ExampleBadCharacter, LEN(bad_chars + 'X') - 1)), @ExampleBadCharacter, '')
FROM   @YourTable
       CROSS APPLY (SELECT REPLACE(
                                TRANSLATE(YourColumn, 
                                          @CharactersToKeep, 
                                          REPLICATE(LEFT(@CharactersToKeep, 1), LEN(@CharactersToKeep))), 
                                LEFT(@CharactersToKeep, 1), 
                                '')) ca(bad_chars)

야간 프로세스에서 제거하고 별도의 필드에 저장한 다음 프로세스를 실행하기 직전에 변경된 레코드를 업데이트할 수 있습니까?

또는 나중에 참조할 수 있도록 삽입/업데이트에 "숫자" 형식을 저장합니다.방아쇠를 당기는 것이 그것을 하는 쉬운 방법이 될 것입니다.

Scott의 CLR 기능을 먼저 시도해보고 싶지만 업데이트되는 레코드 수를 줄이기 위해 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

명백한 이유로, 바하르를 사용하는 것은 수치를 사용하는 것에 비해 근본적으로 느리고 비효율적입니다.원래 게시물에서 연결하는 함수는 문자열의 각 문자를 순환하여 숫자인지 여부를 결정하기 때문에 실제로 매우 느립니다.수천 개의 레코드에 대해 그렇게 하면 프로세스가 느려질 수밖에 없습니다.이 작업은 정규식에 적합하지만 SQL Server에서는 기본적으로 지원되지 않습니다.CLR 기능을 사용하여 지원을 추가할 수 있지만, 시도하지 않고 얼마나 느릴지 말하기는 어렵습니다. 하지만 각 전화 번호의 각 문자를 반복하는 것보다 훨씬 빠를 것으로 예상합니다!

전화 번호가 숫자로만 구성되도록 데이터베이스에 형식을 지정한 후에는 SQL에서 숫자 유형으로 전환하여 다른 숫자 유형과 빠르게 비교할 수 있습니다.새 데이터가 들어오는 속도에 따라 데이터베이스 측에서 트리밍 및 숫자로 변환하는 작업은 비교 대상이 적절하게 포맷되면 충분히 빠르게 수행할 수 있지만 가능하면 에 가져오기 유틸리티를 작성하는 것이 좋습니다.데이터베이스에 도달하기 전에 이러한 포맷 문제를 처리하는 NET 언어입니다.

그러나 어느 쪽이든 선택적 포맷과 관련하여 큰 문제가 발생할 것입니다.귀하의 번호가 북미 원산지로만 보장되더라도 일부 사람들은 지역 번호가 지정된 전화 번호 앞에 1을 붙이고 다른 사람들은 그렇지 않을 것이며, 이로 인해 동일한 전화 번호가 여러 개 입력될 가능성이 있습니다.또한 데이터가 나타내는 내용에 따라 여러 명이 거주하는 집 전화 번호를 사용하는 사람도 있으므로 고유한 제약 조건으로 가구당 한 명의 데이터베이스 구성원만 사용할 수 있습니다.어떤 사람들은 그들의 직장 번호를 사용하고 같은 문제를 가지고 있을 것이고, 어떤 사람들은 인위적인 고유성 잠재력을 다시 유발하는 확장을 포함하거나 포함하지 않을 것입니다.

이 모든 것이 특정 데이터와 사용량에 따라 영향을 줄 수도 있고 그렇지 않을 수도 있지만, 명심해야 할 것은 중요합니다!

성능 측면에서 인라인 기능을 사용할 것입니다. 아래를 참조하십시오.'+', '-' 등의 기호는 제거되지 않습니다.

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자 이상으로 정의할 수 있습니다...

"SQL을 더 이상 문제의 원인으로 분리할 수는 없지만, 여전히 그런 것 같습니다."

SQL Profiler를 실행하고 확인합니다.결과 쿼리를 가져와 실행 계획을 확인하여 인덱스가 사용되고 있는지 확인합니다.

수천 개의 레코드에 대한 수천 개의 레코드는 일반적으로 문제가 되지 않습니다.저는 SSIS를 사용하여 수백만 개의 레코드를 이러한 디듀핑으로 가져옵니다.

우선 데이터베이스를 정리하여 숫자가 아닌 문자를 제거하고 사용하지 않도록 할 것입니다.

매우 간단한 솔루션을 찾고 있습니다.

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

데이터베이스의 전화 번호에 대해 엄격한 형식을 적용할 것을 권장합니다.저는 다음과 같은 형식을 사용합니다.(미국 전화 번호로 가정)

데이터베이스: 55555555x555

디스플레이 : (555) 555-5555 내선 555

입력: 10자리 이상의 숫자가 문자열에 포함되어 있습니다. (Regex 바꾸기는 숫자가 아닌 모든 문자를 제거합니다.)

언급URL : https://stackoverflow.com/questions/106206/fastest-way-to-remove-non-numeric-characters-from-a-varchar-in-sql-server