Remove only leading or trailing carriage returns

Question

I'm dumbfounded that this question has not been asked meaningfully already. How does one go about creating an equivalent function in SQL like LTRIM or RTRIM for carriage returns and line feeds ONLY at the start or end of a string.

Obviously REPLACE(REPLACE(@MyString,char(10),''),char(13),'') removes ALL carriage returns and new line feeds. Which is NOT what I'm looking for. I just want to remove leading or trailing ones.

Solution 1

Find the first character that is not CHAR(13) or CHAR(10) and subtract its position from the string's length.

LTRIM()

SELECT RIGHT(@MyString,LEN(@MyString)-PATINDEX('%[^'+CHAR(13)+CHAR(10)+']%',@MyString)+1)

RTRIM()

SELECT LEFT(@MyString,LEN(@MyString)-PATINDEX('%[^'+CHAR(13)+CHAR(10)+']%',REVERSE(@MyString))+1)

Solution 2

Following functions are enhanced types of trim functions you can use. Copied from sqlauthority.com

These functions remove trailing spaces, leading spaces, white space, tabs, carriage returns, line feeds etc.

Trim Left

CREATE FUNCTION dbo.LTrimX(@str VARCHAR(MAX)) RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @trimchars VARCHAR(10)
SET @trimchars = CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)
IF @str LIKE '[' + @trimchars + ']%' SET @str = SUBSTRING(@str, PATINDEX('%[^' + @trimchars + ']%', @str), LEN(@str))
RETURN @str
END

Trim Right

CREATE FUNCTION dbo.RTrimX(@str VARCHAR(MAX)) RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @trimchars VARCHAR(10)
SET @trimchars = CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)
IF @str LIKE '%[' + @trimchars + ']'
SET @str = REVERSE(dbo.LTrimX(REVERSE(@str)))
RETURN @str
END

Trim both Left and Right

CREATE FUNCTION dbo.TrimX(@str VARCHAR(MAX)) RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN dbo.LTrimX(dbo.RTrimX(@str))
END

Using function

SELECT dbo.TRIMX(@MyString)

If you do use these functions you might also consider changing from varchar to nvarchar to support more encodings.

Solution 3

In SQL Server 2017 you can use the TRIM function to remove specific characters from beginning and end, in one go:

WITH testdata(str) AS (
    SELECT CHAR(13) + CHAR(10) + ' test ' + CHAR(13) + CHAR(10)
)
SELECT
    str,
    TRIM(CHAR(13) + CHAR(10) + CHAR(9) + ' ' FROM str) AS [trim cr/lf/tab/space],
    TRIM(CHAR(13) + CHAR(10) FROM str) AS [trim cr/lf],
    TRIM(' ' FROM str) AS [trim space]
FROM testdata

Result:

Note that the last example (trim space) does nothing as expected since the spaces are in the middle.

Solution 4

Here's an example you may run:

I decided to cast the results as an Xml value, so when you click on it, you will be able to view the Carriage Returns.

DECLARE @CRLF Char(2) = (CHAR(0x0D) + CHAR(0x0A))
DECLARE @String VarChar(MAX) = @CRLF + @CRLF + '    Hello' + @CRLF + 'World  ' + @CRLF + @CRLF
--Unmodified String:
SELECT CAST(@String as Xml)[Unmodified]
--Remove Trailing Whitespace (including Spaces).
SELECT CAST(LEFT(@String, LEN(REPLACE(@String, @CRLF, '  '))) as Xml)[RemoveTrailingWhitespace]
--Remove Leading Whitespace (including Spaces).
SELECT CAST(RIGHT(@String, LEN(REVERSE(REPLACE(@String, @CRLF, '  ')))) as Xml)[RemoveLeadingWhitespace]
--Remove Leading & Trailing Whitespace (including Spaces).
SELECT CAST(SUBSTRING(@String, LEN(REPLACE(@String, ' ', '_')) - LEN(REVERSE(REPLACE(@String, @CRLF, '  '))) + 1, LEN(LTRIM(RTRIM(REPLACE(@String, @CRLF, '  '))))) as Xml)[RemoveAllWhitespace]
--Remove Only Leading and Trailing CR/LF's (while still preserving all other Whitespace - including Spaces). - 04/06/2016 - MCR.
SELECT CAST(SUBSTRING(@String, PATINDEX('%[^'+CHAR(13)+CHAR(10)+']%',@String), LEN(REPLACE(@String, ' ', '_')) - PATINDEX('%[^'+CHAR(13)+CHAR(10)+']%',@String) + 1 - PATINDEX('%[^'+CHAR(13)+CHAR(10)+']%', REVERSE(@String)) + 1) as Xml)[RemoveLeadingAndTrailingCRLFsOnly]

Remember to remove the Cast-to-Xml, as this was done just as a Proof-of-Concept to show it works.

How is this better than the currently Accepted Answer?

At first glance this may appear to use more Functions than the Accepted Answer.
However, this is not the case.
If you combine both approaches listed in the Accepted Answer (to remove both Trailing and Leading whitespace), you will either have to make two passes updating the Record, or copy all of one Logic into the other (everywhere @String is listed), which would cause way more function calls and become even more difficult to read.

View more solutions

Comments

[Dave]

This is good, but fails if the string only contains CRLF characters. PATINDEX returns 0 in this case, so the whole string is returned.

[Matt Roy]

And to remove all of them (tab, CR/LF, spaces), leading and trailing, we do: SELECT CAST(SUBSTRING(@String, PATINDEX('%[^'+CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)+']%',@Stri‌​ng), LEN(REPLACE(@String, ' ', '_')) - PATINDEX('%[^'+CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)+']%',@Stri‌​ng) + 1 - PATINDEX('%[^'+CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)+']%', REVERSE(@String)) + 1) as Xml)[RemoveLeadingAndTrailingCRLFsOnly]

[Ben]

This example shows capabilities of the 2017 TRIM() function that would not have been obvious to the casual developer. As he has shown the function accepts parameters in a syntax I have not seen anywhere before. TRIM ( [ characters FROM ] string ) Here is a sample SQL Fiddle if you want to try it yourself.