Left Trim Newlines in TSQL

Question

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

Pontefract

Answer 1

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).

Mathewson

Answer 2

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

Runion

It doesn't work as ltrim() since your approach will only remove the first crlf. It does fix the example though – Sparkie

you can do substring(field, patindex('%[^' + char(13) + char(10) + ']%', @str), len(field)) – Sparkie

You can keep it in ;) – Sparkie

if you change the pattern to '%[^' + char(13) + char(10) + ',^ ]%' it will skip whitespace also ;-) – Sparkie

I'm not sure if the OP want that, in my demo data I added space between newline to be sure that the query will stop at them – Runion

Answer 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.

Pontefract

Answer 4

This will remove new lines from the data.

SELECT REPLACE(REPLACE(YourColumn, CHAR(13), ''), CHAR(10), '') AS CleanedColumn
FROM YourTable;

Faker

Answer 5

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]

Casebound

RTRIM and LTRIM don't remove CR LF, if you try to run SELECT LEN(LTRIM(char(13) + char(10) + 'Foo')) it'll return 5 not 3 – Runion