Przycinanie białych znaków (spacje, tabulatory, znaki nowej linii)

Korzystam z programu SQL Server 2014 i muszę wyczyścić białe znaki od początku i na końcu zawartości kolumny, gdzie białe znaki mogą być prostymi spacjami, tabulatorami lub znakami nowej linii (zarówno \ni \r\n); na przykład

'    this content    '                          should become 'this content'
'  \r\n   \t\t\t this \r\n content \t  \r\n   ' should become 'this \r\n content'

i tak dalej.

Byłem w stanie osiągnąć tylko pierwszy przypadek

UPDATE table t SET t.column = LTRIM(RTRIM(t.column))

ale w innych przypadkach to nie działa.

Dla każdego używającego SQL Server 2017 lub nowszego

możesz użyć wbudowanej funkcji TRIM . Na przykład:

DECLARE @Test NVARCHAR(4000);
SET @Test = N'  
    ' + NCHAR(0x09) + N'  ' + NCHAR(0x09) + N' this 
 ' + NCHAR(0x09) + NCHAR(0x09) + N'  content' + NCHAR(0x09) + NCHAR(0x09) + N'  
' + NCHAR(0x09) + N' ' + NCHAR(0x09) + NCHAR(0x09) + N'     ';

SELECT N'~'
        + TRIM(NCHAR(0x09) + NCHAR(0x20) + NCHAR(0x0D) + NCHAR(0x0A) FROM @Test)
        + N'~';

Pamiętaj, że domyślnym zachowaniem TRIMjest usuwanie tylko spacji, więc aby usunąć tabulacje i znaki nowej linii (CR + LF), musisz określić characters FROMklauzulę.

Użyłem NCHAR(0x09)również znaków tabulacji w @Testzmiennej, aby przykładowy kod można było skopiować i wkleić i zachować prawidłowe znaki. W przeciwnym razie karty są konwertowane na spacje podczas renderowania tej strony.

Dla każdego używającego SQL Server 2016 lub starszego

Możesz utworzyć funkcję, jako SQLCLR Scalar UDF lub T-SQL Inline TVF (iTVF). T-SQL Inline TVF wyglądałby następująco:

CREATE
--ALTER
FUNCTION dbo.TrimChars(@OriginalString NVARCHAR(4000), @CharsToTrim NVARCHAR(50))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
WITH cte AS
(
  SELECT PATINDEX(N'%[^' + @CharsToTrim + N']%', @OriginalString) AS [FirstChar],
         PATINDEX(N'%[^' + @CharsToTrim + N']%', REVERSE(@OriginalString)) AS [LastChar],
        LEN(@OriginalString + N'~') - 1 AS [ActualLength]
)
SELECT cte.[ActualLength],
       [FirstChar],
       ((cte.[ActualLength] - [LastChar]) + 1) AS [LastChar],
       SUBSTRING(@OriginalString, [FirstChar],
                 ((cte.[ActualLength] - [LastChar]) - [FirstChar] + 2)) AS [FixedString]
FROM   cte;
GO

I uruchom go w następujący sposób:

DECLARE @Test NVARCHAR(4000);
SET @Test = N'  
    ' + NCHAR(0x09) + N'  ' + NCHAR(0x09) + N' this 
 ' + NCHAR(0x09) + NCHAR(0x09) + N'  content' + NCHAR(0x09) + NCHAR(0x09) + N'  
' + NCHAR(0x09) + N' ' + NCHAR(0x09) + NCHAR(0x09) + N'     ';

SELECT N'~' + tc.[FixedString] + N'~' AS [proof]
FROM   dbo.TrimChars(@Test, NCHAR(0x09) + NCHAR(0x20) + NCHAR(0x0D) + NCHAR(0x0A)) tc;

Zwroty:

proof
----
~this 
              content~

I możesz tego użyć za UPDATEpomocą CROSS APPLY:

UPDATE tbl
SET    tbl.[Column] = itvf.[FixedString]
FROM   SchemaName.TableName tbl
CROSS APPLY  dbo.TrimChars(tbl.[Column],
                           NCHAR(0x09) + NCHAR(0x20) + NCHAR(0x0D) + NCHAR(0x0A)) itvf

Jak wspomniano na początku, jest to również bardzo łatwe dzięki SQLCLR, ponieważ .NET zawiera Trim()metodę, która wykonuje dokładnie taką operację, jakiej chcesz. Możesz albo zakodować swój własny kod wywołujący SqlString.Value.Trim(), albo możesz po prostu zainstalować darmową wersję biblioteki SQL # (którą utworzyłem, ale ta funkcja jest w wersji darmowej) i użyć albo String_Trim (który robi tylko białe znaki ) lub String_TrimChars gdzie przekazujesz znaki do przycinania z obu stron (tak jak pokazano powyżej iTVF).

DECLARE @Test NVARCHAR(4000);
SET @Test = N'  
    ' + NCHAR(0x09) + N'  ' + NCHAR(0x09) + N' this 
 ' + NCHAR(0x09) + NCHAR(0x09) + N'  content' + NCHAR(0x09) + NCHAR(0x09) + N'  
' + NCHAR(0x09) + N' ' + NCHAR(0x09) + NCHAR(0x09) + N'     ';

SELECT N'~' + SQL#.String_Trim(@Test) + N'~' AS [proof];

I zwraca dokładnie ten sam ciąg, jak pokazano powyżej w przykładowym wyjściu iTVF. Ale będąc skalarnym UDF, użyłbyś go w następujący sposób UPDATE:

UPDATE tbl
SET    tbl.[Column] = SQL#.String_Trim(itvf.[Column])
FROM   SchemaName.TableName tbl

Każdy z powyższych powinien być efektywny w użyciu w milionach wierszy. Inline TVF są optymalizowane w przeciwieństwie do TVF z wieloma instrukcjami i skalarnych UDF T-SQL. Skalarne UDF SQLCLR mają potencjał do zastosowania w planach równoległych, o ile są oznaczone jako IsDeterministic=truei nie ustawiają żadnego typu DataAccess na Read(domyślny dla dostępu do danych użytkownika i systemu to None), a oba te warunki są true dla obu wymienionych powyżej funkcji SQLCLR.

Możesz rozważyć użycie TVF (funkcja o wartościach w tabeli), aby usunąć obrażające znaki na początku i na końcu danych.

Utwórz tabelę do przechowywania danych testowych:

IF COALESCE(OBJECT_ID('dbo.TrimTest'), 0) <> 0
BEGIN
    DROP TABLE dbo.TrimTest;
END
CREATE TABLE dbo.TrimTest
(
    SampleData VARCHAR(50) NOT NULL
);

INSERT INTO dbo.TrimTest (SampleData)
SELECT CHAR(13) + CHAR(10) + CHAR(9) + 'this is ' + CHAR(13) + CHAR(10) + ' a test' + CHAR(13) + CHAR(10);
GO

Utwórz TVF:

IF COALESCE(OBJECT_ID('dbo.StripCrLfTab'), 0) <> 0
BEGIN
    DROP FUNCTION dbo.StripCrLfTab;
END
GO
CREATE FUNCTION dbo.StripCrLfTab
(
    @val NVARCHAR(1000)
)
RETURNS @Results TABLE
(
    TrimmedVal NVARCHAR(1000) NULL
)
AS
BEGIN
    DECLARE @TrimmedVal NVARCHAR(1000);
    SET @TrimmedVal = CASE WHEN RIGHT(@val, 1) = CHAR(13) OR RIGHT(@val, 1) = CHAR(10) OR RIGHT(@val, 1) = CHAR(9)
            THEN LEFT(
                CASE WHEN LEFT(@val, 1) = CHAR(13) OR LEFT(@val, 1) = CHAR(10) OR LEFT(@val, 1) = CHAR(9)
                THEN RIGHT(@val, LEN(@val) - 1)
                ELSE @val
                END
                , LEN(@val) -1 )
            ELSE
                CASE WHEN LEFT(@val, 1) = CHAR(13) OR LEFT(@val, 1) = CHAR(10) OR LEFT(@val, 1) = CHAR(9)
                THEN RIGHT(@val, LEN(@val) - 1)
                ELSE @val
                END
            END;
    IF @TrimmedVal LIKE (CHAR(13) + '%')
        OR @TrimmedVal LIKE (CHAR(10) + '%')
        OR @TrimmedVal LIKE (CHAR(9) + '%')
        OR @TrimmedVal LIKE ('%' + CHAR(13))
        OR @TrimmedVal LIKE ('%' + CHAR(10))
        OR @TrimmedVal LIKE ('%' + CHAR(9))
        SELECT @TrimmedVal = tv.TrimmedVal
        FROM dbo.StripCrLfTab(@TrimmedVal) tv;
    INSERT INTO @Results (TrimmedVal)
    VALUES (@TrimmedVal);
    RETURN;
END;
GO

Uruchom TVF, aby wyświetlić wyniki:

SELECT tt.SampleData
    , stt.TrimmedVal
FROM dbo.TrimTest tt
CROSS APPLY dbo.StripCrLfTab(tt.SampleData) stt;

Wyniki:

TVF wywołuje się rekurencyjnie, dopóki na początku i na końcu ciągu znaków nie zostaną przekazane funkcje. Jest mało prawdopodobne, aby działało to dobrze w dużej liczbie wierszy, ale prawdopodobnie działałoby dobrze, jeśli używasz tego do naprawy danych podczas wstawiania do bazy danych.

Możesz użyć tego w instrukcji aktualizacji:

UPDATE dbo.TrimTest
SET TrimTest.SampleData = stt.TrimmedVal
FROM dbo.TrimTest tt
CROSS APPLY dbo.StripCrLfTab(tt.SampleData) stt;


SELECT *
FROM dbo.TrimTest;

Wyniki (jako tekst):

Właśnie miałem problem z tą konkretną sytuacją, musiałem znaleźć i wyczyścić każde pole białymi spacjami, ale znalazłem 4 rodzaje możliwych białych spacji w polach bazy danych (odwołanie do tabeli kodów ASCII):

• Zakładka pozioma (char (9))
• Nowa linia (char (10))
• Zakładka pionowa (char (9))
• Spacja (char (32))

Może to zapytanie może ci pomóc.

UPDATE @TABLE SET @COLUMN = replace(replace(replace(replace(@COLUMN,CHAR(9),''),CHAR(10),''),CHAR(13),''),CHAR(32),'')

Musisz przeanalizować drugi przykład, ponieważ LTRIM / RTRIM tylko przycinają spacje. Naprawdę chcesz przyciąć to, co SQL uważa za dane (/ r, / t itp.). Jeśli znasz wartości, których szukasz, po prostu użyj REPLACE, aby je zastąpić. Jeszcze lepiej, napisz funkcję i wywołaj ją.

Jeśli chcesz, skorzystaj z mojej eleganckiej funkcji:

CREATE FUNCTION s_Trim
(
    @s nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
    -- Create comparators for LIKE operator
    DECLARE @whitespaces nvarchar(50) = CONCAT('[ ', CHAR(9), CHAR(10), CHAR(13), ']'); -- Concat chars that you consider as whitespaces
    DECLARE @leftComparator nvarchar(50) = @whitespaces + '%',
            @rightComparator nvarchar(50) = '%' + @whitespaces;
    -- LTRIM
    WHILE @s LIKE @leftComparator AND LEN(@s + 'x') > 1 SET @s = RIGHT(@s, LEN(@s + 'x') - 2)
    -- RTRIM
    WHILE @s LIKE @rightComparator AND LEN(@s + 'x') > 1 SET @s = LEFT(@s, LEN(@s + 'x') - 2)

    RETURN @s;
END
GO

Korzystanie z funkcji w przypadku dużych danych może trwać długo. Mam zestaw danych składający się z 8 milionów wierszy, użycie funkcji zajęło mi ponad 30 minut. replace(replace(replace(replace(@COLUMN,CHAR(9),''),CHAR(10),''),CHAR(13),''),CHAR(32),'')zajęło tylko 5 sekund. Dziękuje wszystkim. Widzę cię @ sami.almasagedi i @Colin 't Hart