SQL Remove only leading or trailing carriage retur

问题:

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.

回答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)

回答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), 8000)
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)

回答3:

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.

回答4:

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:

+----------------+---------------------+------------+----------------+
|str             |trim cr/lf/tab/space |trim cr/lf  |trim space      |
+----------------+---------------------+------------+----------------+
|␍␊␠test␠␍␊ |test                 |␠test␠    |␍␊␠test␠␍␊ |
+----------------+---------------------+------------+----------------+

Note that the last example (trim space) does nothing as expected.