Left Trim Newlines in TSQL

问题:

I try to left trim the newlines in tsql. So I want to remove the leading CHAR(13) + CHAR(10) of my data in a Field. And ensure that the other newlines will not be removed.

I mean this:

'
Kanne Oliver
Rosa-Luxemburg-Str. 3
07817 Alton ( Elster)'

Should be this:

'Kanne Oliver
Rosa-Luxemburg-Str. 3
07817 Alton ( Elster)'

Thanks in advance :D

回答1:

If every row of the data have the CR LF at the begin just strip them from the rows

SELECT SUBSTRING(3, LEN(field)) field
FROM   Table

otherwise, if not all rows begin with CR LF you need to check from it

SELECT CASE WHEN CHAR(13) + CHAR(10) = LEFT(field, 2)
            THEN SUBSTRING(field, 3, LEN(field))
            ELSE field
       END
FROM   Table

---

The queries before this will only remove the first CR LF, to remove any number of them it' possible to use recursive CTE

WITH S AS (
  SELECT sentence 
       = CASE WHEN char(13) + char(10) = LEFT(sentence, 2) 
              THEN SUBSTRING(sentence, 3, LEN(sentence))
              ELSE sentence
         END
  FROM   Test
  UNION ALL
  SELECT sentence = SUBSTRING(sentence, 3, LEN(sentence))
  FROM   S
  WHERE  char(13) + char(10) = LEFT(sentence, 2)
)
select Sentence
FROM   S
WHERE  char(13) + char(10) <> LEFT(sentence, 2)

or, as Filip De Vos pointed out in a comment, search for the first char that is not CR LF

SELECT SUBSTRING(sentence
               , PATINDEX('%[^' + char(13) + char(10) + ']%', sentence)
               , LEN(sentence))
FROM   test

SQLFiddle demo with both queries

回答2:

In this very specific example, SQL Server 2017's TRIM() function could be used. It removes characters from both the beginning and end of a string to text. By default TRIM removes space CHAR(32) characters only. Here, the characters to be removed (CR and LF) will be specified:

SELECT TRIM( CHAR(13) + CHAR(10) FROM '
Kanne Oliver
Rosa-Luxemburg-Str. 3
07817 Alton ( Elster)');

NOTE: in SQL Server Management Studio, it will be helpful to set "Query Results to Text" (CTRL + T) to effectively see the results:

---------------------------------------------------------------
Kanne Oliver
Rosa-Luxemburg-Str. 3
07817 Alton ( Elster)

(1 row affected)

Also note that if there were any CR or LF characters at the end of the string of text, those would be removed too. Unfortunately, RTRIM and LTRIM still only work for space characters (as of SQL 2017).

回答3:

    SET ANSCHRIFT =
    CASE 
    WHEN LEFT(LTRIM(SUBSTRING(ANSCHRIFT, 5, LEN(ANSCHRIFT)-4)),2) = CHAR(13)+ CHAR(10)
    THEN SUBSTRING(LTRIM(SUBSTRING(ANSCHRIFT, 5,LEN(ANSCHRIFT)-4)),3,LEN(LTRIM(SUBSTRING(ANSCHRIFT, 5, LEN(ANSCHRIFT)-4)))-2)
    ELSE LTRIM(SUBSTRING(ANSCHRIFT, 5, LEN(ANSCHRIFT)-4))
    END

Has managed this!

ANSCHRIFT stands for the column name which contained the data. Thanks for the ideas and help.

回答4:

The RTRIM function in Transact-SQL returns a character string after truncating all trailing blanks while LTRIM returns a character expression after it removes leading blanks.

So to remove leading or trailing blanks from a string, you could just write as below:

SELECT RTRIM(LTRIM(YourColumnName)) AS FirstName FROM [dbo].[StaffInformation]