SQL Replace 함수 내부의 정규식 패턴입니까?

SQL Replace 함수 내부의 정규식 패턴입니까?

SELECT REPLACE('<strong>100</strong><b>.00 GB', '%^(^-?\d*\.{0,1}\d+$)%', '');

번호의 두 부분 사이의 마크업을 위의 regex로 치환하고 싶은데, 작동하지 않는 것 같습니다.요, 제가 ''라는 에 틀린 인지 잘 '%[^0-9]%'테스트하려고 했는데도 효과가 없었어요.거거 어어 는는 는는 는? ???

패턴(문자열) 발생의 첫 번째 인덱스를 검색하려면 PATINDEX를 사용할 수 있습니다.그런 다음 STUP을 사용하여 일치하는 패턴(문자열)에 다른 문자열을 넣습니다.

각 행을 루프합니다.각 잘못된 문자를 원하는 문자로 바꿉니다.이 경우 숫자가 아닌 것을 공백으로 바꿉니다.내부 루프는 현재 셀에 잘못된 문자가 여러 개 있는 경우 루프의 문자입니다.

DECLARE @counter int

SET @counter = 0

WHILE(@counter < (SELECT MAX(ID_COLUMN) FROM Table))
BEGIN  

    WHILE 1 = 1
    BEGIN
        DECLARE @RetVal varchar(50)

        SET @RetVal =  (SELECT Column = STUFF(Column, PATINDEX('%[^0-9.]%', Column),1, '')
        FROM Table
        WHERE ID_COLUMN = @counter)

        IF(@RetVal IS NOT NULL)       
          UPDATE Table SET
          Column = @RetVal
          WHERE ID_COLUMN = @counter
        ELSE
            break
    END

    SET @counter = @counter + 1
END

주의:근데 이거 느리네!varchar 컬럼이 있으면 영향이 있을 수 있습니다.따라서 LTRIM RTRIM을 사용하면 도움이 될 수 있습니다.어쨌든, 그것은 느리다.

이 StackOverFlow 응답은 신용이 됩니다.

EDIT Credit도 @srutzky로 이동합니다.

편집(@Tmdean 기준) 이 답변은 한 번에 한 줄씩 수행하는 것이 아니라 보다 세트 기반 솔루션에 맞게 조정할 수 있습니다.한 줄에 숫자가 아닌 문자의 최대 수를 반복하기 때문에 이상적이지는 않지만 대부분의 경우 허용될 수 있다고 생각합니다.

WHILE 1 = 1 BEGIN
    WITH q AS
        (SELECT ID_Column, PATINDEX('%[^0-9.]%', Column) AS n
        FROM Table)
    UPDATE Table
    SET Column = STUFF(Column, q.n, 1, '')
    FROM q
    WHERE Table.ID_Column = q.ID_Column AND q.n != 0;

    IF @@ROWCOUNT = 0 BREAK;
END;

또한 테이블에서 필드가 아직 스크럽되었는지 여부를 나타내는 비트 열을 유지하면 효율성을 크게 향상시킬 수 있습니다(예에서는 NULL은 "알 수 없음"을 나타내며 열 기본값이어야 합니다).

DECLARE @done bit = 0;
WHILE @done = 0 BEGIN
    WITH q AS
        (SELECT ID_Column, PATINDEX('%[^0-9.]%', Column) AS n
        FROM Table
        WHERE COALESCE(Scrubbed_Column, 0) = 0)
    UPDATE Table
    SET Column = STUFF(Column, q.n, 1, ''),
        Scrubbed_Column = 0
    FROM q
    WHERE Table.ID_Column = q.ID_Column AND q.n != 0;

    IF @@ROWCOUNT = 0 SET @done = 1;

    -- if Scrubbed_Column is still NULL, then the PATINDEX
    -- must have given 0
    UPDATE table
    SET Scrubbed_Column = CASE
        WHEN Scrubbed_Column IS NULL THEN 1
        ELSE NULLIF(Scrubbed_Column, 0)
    END;
END;

스키마를 변경하지 않으려면 중간 결과를 테이블 값 변수에 저장하도록 조정하여 마지막에 실제 테이블에 적용할 수 있습니다.

, 하세요.Replace(Column, BadFoundCharacter, '')훨씬 더 빠를 수 있습니다.또한 각 열에 있는 하나의 불량 문자를 교체하는 대신 발견된 문자를 모두 대체합니다.

WHILE 1 = 1 BEGIN
    UPDATE dbo.YourTable
    SET Column = Replace(Column, Substring(Column, PatIndex('%[^0-9.-]%', Column), 1), '')
    WHERE Column LIKE '%[^0-9.-]%'
    If @@RowCount = 0 BREAK;
END;

나는 이것이 단지 더 적은 조작을 하기 때문에 받아들여진 대답보다 더 잘 될 것이라고 확신한다.다른 방법이 더 빠를 수도 있지만, 지금은 그것들을 살펴볼 시간이 없습니다.

일반적으로 SQL Server는 정규식을 지원하지 않으므로 기본 T-SQL 코드에서 정규식을 사용할 수 없습니다.

이를 위해 CLR 함수를 작성할 수 있습니다.예를 들어 여기를 참조하십시오.

퍼포먼스와 용이한 솔루션을 요구하고 있으며, CLR을 유효하게 할 의향이 있는 고객:

CREATE database TestSQLFunctions
go
use TestSQLFunctions
go
ALTER database TestSQLFunctions set trustworthy on

EXEC sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE
go

CREATE ASSEMBLY [SQLFunctions]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103004BE8B85F0000000000000000E00022200B013000000800000006000000000000C2270000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000702700004F000000004000009803000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000C8070000002000000008000000020000000000000000000000000000200000602E72737263000000980300000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E00000000000000000000000000004000004200000000000000000000000000000000A4270000000000004800000002000500682000000807000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000003A022D02022A020304281000000A2A1E02281100000A2A0042534A4201000100000000000C00000076342E302E33303331390000000005006C00000018020000237E000084020000CC02000023537472696E6773000000005005000004000000235553005405000010000000234755494400000064050000A401000023426C6F620000000000000002000001471500000900000000FA0133001600000100000013000000020000000200000003000000110000000F00000001000000030000000000CA01010000000000060025014F02060092014F02060044001D020F006F02000006006C00E20106000801E2010600D400E20106007901E20106004501E20106005E01E20106008300E2010600580030020600360030020600B700E20106009E00B0010600AA02DB010A00F300FC010A001F00FC010E00C3027E02000000000100000000000100010001001000C3029D0241000100010050200000000096002E001A0001005F2000000000861817020600040000000100BD0200000200F40100000300B102090017020100110017020600190017020A0029001702100031001702100039001702100041001702100049001702100051001702100059001702100061001702150069001702100071001702100079001702100089001702060099002E001A0081001702060020007B0010012E000B002A002E00130033002E001B0052002E0023005B002E002B006D002E0033006D002E003B006D002E0043005B002E004B0073002E0053006D002E005B006D002E0063008B002E006B00B5002E007300C2000480000001000000000000000000000000009D020000040000000000000000000000210016000000000004000000000000000000000021000A00000000000400000000000000000000002100DB010000000000000000003C4D6F64756C653E0053797374656D2E44617461006D73636F726C696200446174614163636573734B696E64005265706C61636500477569644174747269627574650044656275676761626C6541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C795469746C6541747472696275746500417373656D626C7954726164656D61726B417474726962757465005461726765744672616D65776F726B41747472696275746500417373656D626C7946696C6556657273696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E4174747269627574650053716C46756E6374696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E7341747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C79436F6D70616E794174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053797374656D2E52756E74696D652E56657273696F6E696E670053514C46756E6374696F6E732E646C6C0053797374656D0053797374656D2E5265666C656374696F6E007061747465726E004D6963726F736F66742E53716C5365727665722E536572766572002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E496E7465726F7053657276696365730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E546578742E526567756C617245787072657373696F6E730053514C46756E6374696F6E73004F626A656374007265706C6163656D656E7400696E70757400526567657800000000000000003A1617E607071B47B964858BCD87458B00042001010803200001052001011111042001010E04200101020600030E0E0E0E08B77A5C561934E0890801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000200000000001101000C53514C46756E6374696F6E73000005010000000017010012436F7079726967687420C2A920203230323000002901002434346436386231632D393735312D343938612D396665352D32316666333934303738303900000C010007312E302E302E3000004D01001C2E4E45544672616D65776F726B2C56657273696F6E3D76342E352E320100540E144672616D65776F726B446973706C61794E616D65142E4E4554204672616D65776F726B20342E352E32808F010001005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A4461746141636365737301000000000000982700000000000000000000B2270000002000000000000000000000000000000000000000000000A4270000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000003C03000000000000000000003C0334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B0049C020000010053007400720069006E006700460069006C00650049006E0066006F0000007802000001003000300030003000300034006200300000001A000100010043006F006D006D0065006E007400730000000000000022000100010043006F006D00700061006E0079004E0061006D006500000000000000000042000D000100460069006C0065004400650073006300720069007000740069006F006E0000000000530051004C00460075006E006300740069006F006E00730000000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000042001100010049006E007400650072006E0061006C004E0061006D0065000000530051004C00460075006E006300740069006F006E0073002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003200300000002A00010001004C006500670061006C00540072006100640065006D00610072006B00730000000000000000004A00110001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C00460075006E006300740069006F006E0073002E0064006C006C00000000003A000D000100500072006F0064007500630074004E0061006D00650000000000530051004C00460075006E006300740069006F006E00730000000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000C43700000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE

go

CREATE FUNCTION RegexReplace(
    @input nvarchar(max),
    @pattern nvarchar(max),
    @replacement nvarchar(max)
) RETURNS nvarchar  (max)
AS EXTERNAL NAME SQLFunctions.[SQLFunctions.Regex].Replace; 

go

-- outputs This is a test 
SELECT dbo.RegexReplace('This is a test 12345','[0-9]','')

DLL 내용:

우연히 다른 것을 찾다가 이 투고를 접하게 되었습니다만, 사용하는 솔루션이 훨씬 효율적이며, 세트 베이스의 쿼리와 함께 사용하는 경우 기본 구현이 되어야 합니다.즉, 크로스 어플리케이션 테이블 함수를 사용하는 것입니다.이 주제가 아직 진행 중인 것 같아서 누군가에게 도움이 되었으면 합니다.

recursive set 기반 쿼리 또는 scalar 함수 실행을 기반으로 한 몇 가지 응답에 대한 예제 런타임은 랜덤 newid에서 문자를 제거하는 1m 행 테스트 세트에 기반하며 WHY 루프 예제의 경우 34초에서 2m05초, 함수 예제의 경우 1m3에서 {forever}까지입니다.

표 함수를 크로스 적용과 함께 사용하면 10초 안에 동일한 목표를 달성할 수 있습니다.처리되는 최대 길이 등 필요에 따라 조정해야 할 수 있습니다.

기능:

CREATE FUNCTION [dbo].[RemoveChars](@InputUnit VARCHAR(40))
RETURNS TABLE
AS
RETURN
    (
        WITH Numbers_prep(Number) AS
            (
                SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
            )
        ,Numbers(Number) AS
            (
                SELECT TOP (ISNULL(LEN(@InputUnit),0))
                    row_number() OVER (ORDER BY (SELECT NULL))
                FROM Numbers_prep a
                    CROSS JOIN Numbers_prep b
            )
        SELECT
            OutputUnit
        FROM
            (
                SELECT
                    substring(@InputUnit,Number,1)
                FROM  Numbers
                WHERE substring(@InputUnit,Number,1) like '%[0-9]%'
                ORDER BY Number
                FOR XML PATH('')
            ) Sub(OutputUnit)
    )

사용방법:

UPDATE t
SET column = o.OutputUnit
FROM ##t t
CROSS APPLY [dbo].[RemoveChars](t.column) o

여기 앞의 답변에서 이를 달성하기 위해 작성한 함수가 있습니다.

CREATE FUNCTION dbo.RepetitiveReplace
(
    @P_String VARCHAR(MAX),
    @P_Pattern VARCHAR(MAX),
    @P_ReplaceString VARCHAR(MAX),
    @P_ReplaceLength INT = 1
)
RETURNS VARCHAR(MAX)
BEGIN
    DECLARE @Index INT;

    -- Get starting point of pattern
    SET @Index = PATINDEX(@P_Pattern, @P_String);

    while @Index > 0
    begin
        --replace matching charactger at index
        SET @P_String = STUFF(@P_String, PATINDEX(@P_Pattern, @P_String), @P_ReplaceLength, @P_ReplaceString);
        SET @Index = PATINDEX(@P_Pattern, @P_String);
    end

    RETURN @P_String;
END;

[Gist] [1] [1] : https://gist.github.com/jkdba/ca13fe8f2a9855c4bdbfd0a5d3dfcda2

편집:

원래 여기에는 32개의 네스트레벨 제한이 있기 때문에 SQL Server와 잘 어울리지 않는 재귀함수가 있었습니다.이 함수로 32개 이상의 치환을 시도할 때마다 다음과 같은 오류가 발생합니다.서버 레벨의 변경을 실시해, 보다 많은 네스트를 허가하는(예를 들면, never end loop을 허가하지 않는 등 위험할 가능성이 있다) 것이 아니라, while loop으로 전환하는 것이 훨씬 효과적입니다.

최대 저장 프로시저, 함수, 트리거 또는 뷰 중첩 수준을 초과했습니다(제한 32).

솔루션을 재사용하려는 경우 SQL 함수 내에 래핑하면 유용합니다.세포 레벨에서도 하고 있기 때문에, 이것을 다른 답으로 하고 있습니다.

CREATE FUNCTION [dbo].[fnReplaceInvalidChars] (@string VARCHAR(300))
RETURNS VARCHAR(300)
BEGIN
    DECLARE @str VARCHAR(300) = @string;
    DECLARE @Pattern VARCHAR (20) = '%[^a-zA-Z0-9]%';
    DECLARE @Len INT;
    SELECT @Len = LEN(@String); 
    WHILE @Len > 0 
    BEGIN
        SET @Len = @Len - 1;
        IF (PATINDEX(@Pattern,@str) > 0)
            BEGIN
                SELECT @str = STUFF(@str, PATINDEX(@Pattern,@str),1,'');    
            END
        ELSE
        BEGIN
            BREAK;
        END
    END     
    RETURN @str
END

큰 문자열의 경우 보다 빠른 접근법은 다음과 같습니다.

CREATE FUNCTION [dbo].[fnReplaceInvalidChars] (@string VARCHAR(MAX))
RETURNS VARCHAR(MAX)
BEGIN
    DECLARE @str VARCHAR(MAX) = @string;
    DECLARE @Pattern VARCHAR (MAX) = '%[^a-zA-Z0-9]%';
    WHILE PATINDEX(@Pattern,@str) > 0
    BEGIN
        SELECT @str = STUFF(@str, PATINDEX(@Pattern,@str),1,''); 
    END     
    RETURN @str
END

시간 필드에 숫자가 아닌 문자가 포함된 문자열을 정리하기 위해 이 함수를 만들었습니다.시간에는 회의록을 추가하지 않은 물음표가 포함되어 있습니다. 예를 들어 20:?함수는 각 문자를 루프하여 ?을 0으로 바꿉니다.

 CREATE FUNCTION [dbo].[CleanTime]
(
    -- Add the parameters for the function here
    @intime nvarchar(10) 
)
RETURNS nvarchar(5)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @ResultVar nvarchar(5)
    DECLARE @char char(1)
    -- Add the T-SQL statements to compute the return value here
    DECLARE @i int = 1
    WHILE @i <= LEN(@intime)
    BEGIN
    SELECT @char =  CASE WHEN substring(@intime,@i,1) like '%[0-9:]%' THEN substring(@intime,@i,1) ELSE '0' END
    SELECT @ResultVar = concat(@ResultVar,@char)   
    set @i  = @i + 1       
    END;
    -- Return the result of the function
    RETURN @ResultVar

END

저는 이 솔루션이 더 빠르고 단순하다고 생각합니다.항상 CTE/recursive를 사용하는 이유는WHILESQL Server에서 속도가 너무 느립니다.제가 사용하는 프로젝트 및 대규모 데이터베이스에서 사용합니다.

/*
Function:           dbo.kSql_ReplaceRegExp
Create Date:        20.02.2021
Author:             Karcan Ozbal

Description:        The given string value will be replaced according to the given regexp/pattern.

Parameter(s):       @Value       : Value/Text to REPLACE.
                    @RegExp      : The regexp/pattern to be used for REPLACE operation.

Usage:              select dbo.kSql_ReplaceRegExp('2T3EST5','%[0-9]%')
Output:             'TEST'
*/
ALTER FUNCTION [dbo].[kSql_ReplaceRegExp](
    @Value nvarchar(max),
    @RegExp nvarchar(50)
)
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @Result nvarchar(max)

    ;WITH CTE AS (
        SELECT NUM = 1, VALUE = @Value, IDX = PATINDEX(@RegExp, @Value)
        UNION ALL
        SELECT NUM + 1, VALUE = REPLACE(VALUE, SUBSTRING(VALUE,IDX,1),''), IDX = PATINDEX(@RegExp, REPLACE(VALUE, SUBSTRING(VALUE,IDX,1),'')) 
        FROM CTE
        WHERE IDX > 0
    )
    SELECT TOP(1) @Result = VALUE 
    FROM CTE 
    ORDER BY NUM DESC
    OPTION (maxrecursion 0)

    RETURN @Result
END

Stored Procedure(저장 프로시저)에 들어가는 파라미터에 대해서만 이 작업을 수행하는 경우 다음을 사용할 수 있습니다.

declare @badIndex int
set @badIndex = PatIndex('%[^0-9]%', @Param)
while @badIndex > 0
    set @Param = Replace(@Param, Substring(@Param, @badIndex, 1), '')
    set @badIndex = PatIndex('%[^0-9]%', @Param)

난 이게 더 명확하다고 생각했어.

ALTER FUNCTION [dbo].[func_ReplaceChars](
    @Value nvarchar(max),
    @Chars nvarchar(50)
)
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @cLen int = len(@Chars);
    DECLARE @curChar int = 0;

    WHILE @curChar<@cLen
    BEGIN
        set @Value = replace(@Value,substring(@Chars,@curChar,1),'');

        set @curChar = @curChar + 1;
    END;

    RETURN @Value
END

위의 몇 가지 코드와 유사한 코드를 사용하고 있습니다.

DROP FUNCTION [dbo].[fnCleanString]
GO

CREATE FUNCTION [dbo].[fnCleanString] (@input VARCHAR(max), @Pattern 
VARCHAR (20))
RETURNS VARCHAR(max)
BEGIN
    DECLARE @str VARCHAR(max) = @input;
    DECLARE @Len INT;
    DECLARE @INDEX INT;
    SELECT @Len = LEN(@input); 
    WHILE @Len > 0  
    BEGIN
        SET @INDEX = PATINDEX(@Pattern,@str);
        IF (@INDEX > 0)
            BEGIN
                SET @str=REPLACE(@str,SUBSTRING(@str,@INDEX, 1), '');               
            END         
        ELSE
            BEGIN
                BREAK;
            END
    END     
    RETURN @str
END

다음과 같이 사용할 수 있습니다.

  SELECT CleanName = dbo.[fnCleanString](Name, '%[0-9]%') from YourTable

보다 간단하고 빠른 접근법은 알파벳의 각 문자에 따라 반복된다고 생각합니다.

DECLARE @i int
SET @i = 0

WHILE(@i < 256)
BEGIN  

    IF char(@i) NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '.')      

      UPDATE Table SET Column = replace(Column, char(@i), '')

    SET @i = @i + 1

END

언급URL : https://stackoverflow.com/questions/21378193/regex-pattern-inside-sql-replace-function