SQL Server의 VARCHAR에서 숫자가 아닌 문자를 제거하는 가장 빠른 방법

SQL Server의 VARCHAR에서 숫자가 아닌 문자를 제거하는 가장 빠른 방법

---

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

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

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

그러나 그것은 속도를 높이 지 못했습니다.

숫자가 아닌 문자를 제거하는 더 빠른 방법이 있습니까? 10,000 개에서 100,000 개의 레코드를 비교해야 할 때 잘 수행 할 수있는 것.

무엇을하든 빠르게 수행해야합니다 .

업데이트
사람들의 반응을 감안할 때 가져 오기 유틸리티를 실행하기 전에 필드를 정리해야한다고 생각합니다.

가져 오기 유틸리티를 작성하는 것에 대한 질문에 답하기 위해 C # 앱입니다. DB 데이터를 변경할 필요없이 BIGINT와 BIGINT를 비교하고 있으며 여전히 매우 작은 데이터 세트 (약 2000 개 레코드)로 성능 저하를 겪고 있습니다.

BIGINT와 BIGINT를 비교하면 속도가 느려질 수 있습니까?

가능한 한 내 앱의 코드 측면을 최적화했습니다 (정규식 제거, 불필요한 DB 호출 제거). 더 이상 SQL을 문제의 원인으로 격리 할 수는 없지만 여전히 그런 느낌이 듭니다.

---

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

기존 레코드를 업데이트하려면 한 번만 발생하면되는 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)

---

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,'(',''),' ',''),'-',''),')','')

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

---

간단한 기능 :

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

---

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

또는 삽입 / 업데이트시 나중에 참조 할 수 있도록 "숫자"형식을 저장합니다. 방아쇠를 사용하면 쉽게 할 수 있습니다.

---

Scott의 CLR 함수를 먼저 시도하지만 업데이트 된 레코드 수를 줄이기 위해 WHERE 절을 추가합니다.

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

대부분의 레코드에 숫자가 아닌 문자가 있다는 것을 알고 있다면 도움이되지 않을 수 있습니다.

---

게임이 늦었다는 것을 알고 있지만 여기에 숫자가 아닌 문자를 빠르게 제거하는 T-SQL 용으로 만든 함수가 있습니다. 참고로 문자열에 대한 유틸리티 함수를 넣은 스키마 "문자열"이 있습니다.

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

---

Working with varchars is fundamentally slow and inefficient compared to working with numerics, for obvious reasons. The functions you link to in the original post will indeed be quite slow, as they loop through each character in the string to determine whether or not it's a number. Do that for thousands of records and the process is bound to be slow. This is the perfect job for Regular Expressions, but they're not natively supported in SQL Server. You can add support using a CLR function, but it's hard to say how slow this will be without trying it I would definitely expect it to be significantly faster than looping through each character of each phone number, however!

Once you get the phone numbers formatted in your database so that they're only numbers, you could switch to a numeric type in SQL which would yield lightning-fast comparisons against other numeric types. You might find that, depending on how fast your new data is coming in, doing the trimming and conversion to numeric on the database side is plenty fast enough once what you're comparing to is properly formatted, but if possible, you would be better off writing an import utility in a .NET language that would take care of these formatting issues before hitting the database.

Either way though, you're going to have a big problem regarding optional formatting. Even if your numbers are guaranteed to be only North American in origin, some people will put the 1 in front of a fully area-code qualified phone number and others will not, which will cause the potential for multiple entries of the same phone number. Furthermore, depending on what your data represents, some people will be using their home phone number which might have several people living there, so a unique constraint on it would only allow one database member per household. Some would use their work number and have the same problem, and some would or wouldn't include the extension which would cause artificial uniqueness potential again.

All of that may or may not impact you, depending on your particular data and usages, but it's important to keep in mind!

---

"Although I can't isolate SQL as the source of the problem anymore, I still feel like it is."

Fire up SQL Profiler and take a look. Take the resulting queries and check their execution plans to make sure that index is being used.

---

Thousands of records against thousands of records is not normally a problem. I've used SSIS to import millions of records with de-duping like this.

I would clean up the database to remove the non-numeric characters in the first place and keep them out.

---

Looking for a super simple solution:

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

---

I'd use an Inline Function from performance perspective, see below: 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 */

You can define it with more than 100 characters...

---

I would recommend enforcing a strict format for phone numbers in the database. I use the following format. (Assuming US phone numbers)

Database: 5555555555x555

Display: (555) 555-5555 ext 555

Input: 10 digits or more digits embedded in any string. (Regex replacing removes all non-numeric characters)

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