SQL 바꾸기 함수 내부의 정규식 패턴?

SQL 바꾸기 함수 내부의 정규식 패턴?

---

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

숫자의 두 부분 사이에있는 마크 업을 위의 정규식으로 바꾸고 싶지만 작동하지 않는 것 같습니다. '%[^0-9]%'테스트하기 위해 더 간단한 것을 시도했지만 작동하지 않았기 때문에 잘못된 정규식 구문인지 확실 하지 않습니다. 누구든지 내가 이것을 어떻게 얻을 수 있는지 알고 있습니까?

---

PATINDEX 를 사용 하여 패턴 (문자열) 발생의 첫 번째 인덱스를 찾을 수 있습니다 . 그런 다음 STUFF 를 사용 하여 일치하는 패턴 (문자열)에 다른 문자열을 채 웁니다.

각 행을 반복합니다. 각 잘못된 문자를 원하는 문자로 바꿉니다. 귀하의 경우에는 숫자가 아닌 것을 공백으로 바꾸십시오. 내부 루프는 루프의 현재 셀에 잘못된 문자가 둘 이상있는 경우입니다.

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 답변에 크레딧이 적용됩니다 .

편집 크레딧도 @srutzky로 이동합니다.

Edit (by @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;

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

---

일반적으로 SQL Server는 정규식을 지원하지 않으며 네이티브 T-SQL 코드에서 사용할 수 없습니다.

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

---

발견 된 캐릭터를 유일한 위치로 제거하는 대신 사용하는 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;

나는 이것이 더 적은 작업을 수행하기 때문에 허용되는 답변보다 더 잘 작동한다고 확신합니다. 더 빠를 수있는 다른 방법도 있지만 지금은 탐색 할 시간이 없습니다.

---

나는이 게시물을 우연히 발견하여 다른 것을 찾고 있었지만 훨씬 더 효율적인 솔루션을 언급하고 있다고 생각했습니다. 세트 기반 쿼리와 함께 사용할 때 실제로 모든 기능의 기본 구현이어야합니다. 교차 적용을 사용하는 것입니다. 테이블 기능. 주제가 여전히 활성 상태이므로 누군가에게 유용하기를 바랍니다.

임의의 newid에서 문자를 제거하는 1m 행 테스트 세트를 기반으로 한 재귀 집합 기반 쿼리 또는 스칼라 함수 실행을 기반으로 지금까지 몇 가지 답변에 대한 예제 런타임은 WHILE 루프 예제의 경우 34 초에서 2 분 5 초, 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;

요점

편집하다:

원래는 32 중첩 수준 제한이 있기 때문에 SQL 서버에서 잘 작동하지 않는 재귀 함수가있어 함수로 32 개 이상의 교체를 시도 할 때마다 아래와 같은 오류가 발생합니다. 더 많은 중첩을 허용하기 위해 서버 수준을 변경하는 대신 (종료하지 않는 루프를 허용하는 것처럼 위험 할 수 있음) while 루프로 전환하는 것이 훨씬 더 합리적입니다.

최대 저장 프로 시저, 함수, 트리거 또는 뷰 중첩 수준이 초과되었습니다 (제한 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

---

I've created this function to clean up a string that contained non numeric characters in a time field. The time contained question marks when they did not added the minutes, something like this 20:??. Function loops through each character and replaces the ? with a 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

---

If you are doing this just for a parameter coming into a Stored Procedure, you can use the following:

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)

---

I think a simpler and faster approach is iterate by each character of the alphabet:

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