복붙노트

[SQL] 가장 빠른 방법은 SQL Server에서 VARCHAR에서 숫자가 아닌 문자를 제거합니다

SQL

가장 빠른 방법은 SQL Server에서 VARCHAR에서 숫자가 아닌 문자를 제거합니다

나는 가져 오기 내에서 고유 키로 전화 번호를 사용 가져 오기 유틸리티를 쓰고 있어요.

나는 전화 번호가 이미 내 DB에 존재하지 않는 것을 확인해야합니다. 문제는 DB에 전화 번호가 대시와 괄호 가능성이 다른 것들과 같은 일을 할 수 있다는 것입니다. 나는 문제가 느리고 번 수입에 내 DB에 기록 및 기록의 수천으로,이 과정이 너무 느릴 수 있다는 것입니다, 이러한 것들을 제거하는 기능을 썼다. 난 이미 전화 번호 열 인덱스했습니다.

이 게시물에서 스크립트를 사용하여 시도 : T-SQL 트림 & NBSP (및 기타 영숫자가 아닌 문자)

하지만 그 어떤 그것을 속도를하지 않았다.

숫자가 아닌 문자를 제거하는 빠른 방법이 있나요? 10,000 100,000 기록을 비교해야 할 때 잘 수행 할 수있는 뭔가.

무엇이든 빨리 수행하기 위해 요구 사항을 수행한다.

최신 정보 을 감안할 때 어떤 사람들은 내가 내가 가져 오기 유틸리티를 실행하기 전에 필드를 청소해야 할 것 같아요으로 반응했다.

나는에서 가져 오기 유틸리티를 쓰고 있어요 무엇의 질문에 대답하기 위해, C #을 응용 프로그램입니다. 나는 DB 데이터를 변경할 필요없이, 지금 BIGINT에 BIGINT를 비교 그리고 난 여전히 데이터 (2000에 대한 기록)의 매우 작은 세트와 성능 저하를 복용하고 있습니다.

BIGINT에 비교 BIGINT는 아래 것들을 둔화 될 수 있을까요?

내가 (정규 표현식에, 제거 불필요한 DB 호출을 제거) 수 나는만큼 내 응용 프로그램의 코드면을 최적화했습니다. 비록 문제의 소스로 분리 SQL은처럼 나는 아직도 기분이 더 이상 할 수 없습니다.

해결법

  1. ==============================

    1.나는 오해 할 수 있지만 당신은 당신이 가져올 때마다 다음 현재 데이터베이스의 데이터에 대한 새로운 세트를 하나의 문자열을 제거하기 위해 두 데이터 집합을 가지고있다.

    나는 오해 할 수 있지만 당신은 당신이 가져올 때마다 다음 현재 데이터베이스의 데이터에 대한 새로운 세트를 하나의 문자열을 제거하기 위해 두 데이터 집합을 가지고있다.

    기존 레코드를 업데이트, 난 그냥 한 번만 발생하는 것으로, 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)
  2. ==============================

    2.나는 T-SQL 코드와 PATINDEX이 솔루션을 보았다. 나는 그것을 좋아하지 :-)

    나는 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
  3. ==============================

    3.(교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (대체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (대체 ','C ',' '),'D ',' '),'E '('A ',' '),'B ','(대체 문자열) 교체 '),'F ', ')', g '' '),'H ',' '),'I ',' '),'J ',' '),'K '를'), 'L', ' ), 'm' ''), N '' ''), 'O', ''), 'P', ''), 'Q', ''), R '' ''), 'w'S ',' '),'t ',' '),'U ',' '),'V '' '), ""),'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 문자열로

    (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (대체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (교체 (대체 ','C ',' '),'D ',' '),'E '('A ',' '),'B ','(대체 문자열) 교체 '),'F ', ')', g '' '),'H ',' '),'I ',' '),'J ',' '),'K '를'), 'L', ' ), 'm' ''), N '' ''), 'O', ''), 'P', ''), 'Q', ''), R '' ''), 'w'S ',' '),'t ',' '),'U ',' '),'V '' '), ""),'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 문자열로

    :)

  4. ==============================

    4.혹시 함수를 작성하지 않았거나 T-SQL에서 단지 하나의 인라인 호출을 필요, 당신은 시도 할 수 있습니다 :

    혹시 함수를 작성하지 않았거나 T-SQL에서 단지 하나의 인라인 호출을 필요, 당신은 시도 할 수 있습니다 :

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

    물론이 전화 서식 번호가 아닌 일반 제거 문자열 함수에서 모든 특수 문자를 제거에만 적용됩니다.

  5. ==============================

    5.간단한 기능 :

    간단한 기능 :

    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
  6. ==============================

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

    7.당신이 매일 밤 과정에서 제거 별도의 필드에 저장 할 수 있습니다, 당신은 프로세스를 실행하기 직전에 변경 기록에 대한 업데이 트를합니까?

    당신이 매일 밤 과정에서 제거 별도의 필드에 저장 할 수 있습니다, 당신은 프로세스를 실행하기 직전에 변경 기록에 대한 업데이 트를합니까?

    또는 삽입 / 업데이트에서 "숫자"형식을 저장, 나중에 참조 할 수 있습니다. 트리거는 그것을 할 수있는 쉬운 방법이 될 것입니다.

  8. ==============================

    8.내가 먼저 스캇의 CLR 기능을 시도하지만 절은 업데이트 된 레코드의 수를 줄이기 위해 WHERE 다음을 추가합니다.

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

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

    당신이 당신의 기록의 대다수가 숫자가 아닌 문자가있는 것을 알고 있다면 그래도 도움이되지 않을 수 있습니다.

  9. ==============================

    9.나는 늦게 게임이지만, 여기에 내가 빨리 숫자가 아닌 문자를 제거 T-SQL에 대해 생성하는 기능을 알고있다. 참고로, 난에 문자열 유틸리티 기능을 넣어 스키마 "문자열을"이 ...

    나는 늦게 게임이지만, 여기에 내가 빨리 숫자가 아닌 문자를 제거 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
  10. ==============================

    10.VARCHAR에 대한 작업은 분명한 이유, 수치 작업에 비해 근본적으로 느리고 비효율적이다. 문자열의 각 문자를 통해 그들이 루프는 다수의 여부를 결정하기로 원래 게시물에 링크 기능은 실제로 매우 느려집니다. 기록과 프로세스의 수천 느릴 수밖에 없다 그렇게. 이 정규 표현식에 대한 완벽한 직업,하지만 그들은 기본적으로 SQL 서버에서 지원되지 않는 것입니다. 당신은 CLR 기능을 사용하여 지원을 추가 할 수 있습니다, 그러나 나는 확실히 그러나 각 전화 번호의 각 문자를 통해 반복보다 더 빨리 크게 것으로 기대를 시도하지 않고이 얼마나 천천히 말을하기 어렵다!

    VARCHAR에 대한 작업은 분명한 이유, 수치 작업에 비해 근본적으로 느리고 비효율적이다. 문자열의 각 문자를 통해 그들이 루프는 다수의 여부를 결정하기로 원래 게시물에 링크 기능은 실제로 매우 느려집니다. 기록과 프로세스의 수천 느릴 수밖에 없다 그렇게. 이 정규 표현식에 대한 완벽한 직업,하지만 그들은 기본적으로 SQL 서버에서 지원되지 않는 것입니다. 당신은 CLR 기능을 사용하여 지원을 추가 할 수 있습니다, 그러나 나는 확실히 그러나 각 전화 번호의 각 문자를 통해 반복보다 더 빨리 크게 것으로 기대를 시도하지 않고이 얼마나 천천히 말을하기 어렵다!

    당신은 그들이 숫자 만있어 그래서 데이터베이스에 포맷 된 전화 번호를 받으면, 다른 숫자 유형에 대한 번개 빠른 비교를 얻을 것이다 SQL에서 숫자 형식으로 전환 할 수있다. 당신은 당신의 새로운 데이터가 충분히 빠르게의 형식이 무엇 당신이 비교하고 한 번에 많은되어오고 데이터베이스 측에서 숫자로 트리밍 및 변환을하고 얼마나 빨리에 따라, 그 찾을 수 있지만, 가능하면, 당신은 더 나은 것 데이터베이스를 타격하기 전에 이러한 형식 문제가 알아서 할 .NET 언어에서 가져 오기 유틸리티를 작성 끕니다.

    어느 쪽이든 불구하고, 당신은 옵션 형식에 대한 큰 문제가 될 것입니다. 당신의 숫자가 원산지 만 북미을 보장하는 경우에도, 어떤 사람들은 완전히 지역 코드 자격 전화 번호 앞에 1을 넣어 다른 사람은 동일한 전화 번호의 여러 항목에 대한 가능성의 원인이되는,하지 않습니다. 그것에 고유 제한 조건은 가구당 하나의 데이터베이스 구성원을 허용 할 수 있도록 또한, 데이터가 나타내는 내용에 따라, 어떤 사람들은, 거기에 사는 여러 사람이있을 수 있습니다 자신의 집 전화 번호를 사용하는 것입니다. 일부는 자신의 작품 번호를 사용하고 동일한 문제가, 일부는하거나 다시 인공 고유성 잠재력을 일으킬 것 확장자를 포함하지 않을 것이다 것입니다.

    그 모든 또는 특정 데이터와 용도에 따라 영향을주지 않을 수도 있습니다,하지만 명심하는 것이 중요합니다!

  11. ==============================

    11."이처럼 나는 아직도 느낌, 난 더 이상 문제의 소스로하지 분리 SQL 수 있지만."

    "이처럼 나는 아직도 느낌, 난 더 이상 문제의 소스로하지 분리 SQL 수 있지만."

    SQL 프로파일 러를 화재와 봐. 결과 쿼리를 가지고 있는지 확인 인덱스를 사용하고 있는지 확인하기 위해 자신의 실행 계획을 확인합니다.

  12. ==============================

    12.수천 개의 레코드에 대한 기록의 수천은 일반적으로 문제가되지 않습니다. 나는이 같은 중복 방지 레코드의 수입 수백만 SSIS를 사용했습니다.

    수천 개의 레코드에 대한 기록의 수천은 일반적으로 문제가되지 않습니다. 나는이 같은 중복 방지 레코드의 수입 수백만 SSIS를 사용했습니다.

    나는 처음에 숫자가 아닌 문자를 제거하고 그들을 유지하기 위해 데이터베이스를 정리하는 것입니다.

  13. ==============================

    13.슈퍼 간단한 해결책을 찾고 :

    슈퍼 간단한 해결책을 찾고 :

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

    14.나는 성능의 관점에서 인라인 함수를 사용하는 것, 아래 참조 : '-'등 제거되지 않습니다 '+'와 같은 기호는주의

    나는 성능의 관점에서 인라인 함수를 사용하는 것, 아래 참조 : '-'등 제거되지 않습니다 '+'와 같은 기호는주의

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

  15. ==============================

    15.나는 데이터베이스에있는 전화 번호에 대한 엄격한 형식을 시행에게 추천 할 것입니다. 나는 다음과 같은 형식을 사용합니다. (미국 전화 번호를 가정)

    나는 데이터베이스에있는 전화 번호에 대한 엄격한 형식을 시행에게 추천 할 것입니다. 나는 다음과 같은 형식을 사용합니다. (미국 전화 번호를 가정)

    데이터베이스 : 5555555555x555

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

    입력 : 10 자리 또는 문자열에 포함 이상의 숫자. (정규식은 제거합니다 모든 숫자가 아닌 문자를 대체)

    16. from https://stackoverflow.com/questions/106206/fastest-way-to-remove-non-numeric-characters-from-a-varchar-in-sql-server by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] C # DataTable을에 읽기 SQL 표  (0) 2020.03.28
[SQL] SQL Server의 커버 인덱스 및 대상 쿼리는 무엇입니까?  (0) 2020.03.28
[SQL] 그것은 어떤 깊이로, 하나의 쿼리에서 MySQL은 트리 구조의 테이블을 쿼리 할 수 ​​있습니까?  (0) 2020.03.28
[SQL] SQL 서버, 분할 0을 반환  (0) 2020.03.28
[SQL] 행으로 전체 데이터베이스에 DataTable을 한 번에 대신 행입니까?  (0) 2020.03.28
,

티스토리툴바