仅删除开头或结尾的回车符

Question

我很惊讶这个问题还没有得到有意义的回答。如何在SQL中创建一个类似于LTRIM或RTRIM函数，只用于删除字符串开头或结尾的回车符和换行符。

显然，REPLACE(REPLACE(@MyString,char(10),''),char(13),'')会移除所有回车符和换行符，而这并不是我要找的。我只想删除开头或结尾的。

- dynamphorous

Answer 1

找到第一个不是CHAR(13)或CHAR(10)的字符，并将其位置从字符串长度中减去。

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)

- Anon

---

这很好，但如果字符串只包含CRLF字符，则会失败。在这种情况下，PATINDEX返回0，因此整个字符串被返回。 - Dave

Answer 2

以下是增强版的trim函数，你可以使用它们。这些函数来自sqlauthority.com

这些函数可以去除末尾空格、开始空格、空格、制表符、回车符、换行符等。

左侧裁剪

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

右侧修剪

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

去除左右两侧空白

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

使用函数

SELECT dbo.TRIMX(@MyString)

如果您使用这些函数，您可能还应该考虑从varchar更改为nvarchar以支持更多的编码。

- sqluser

Answer 3

在SQL Server 2017 中，您可以使用 TRIM 函数一次性从开头和结尾删除特定字符：

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

结果:

请注意，最后一个示例（去除空格）不会产生任何效果，因为空格位于中间。

- Salman A

---

这个例子展示了2017年**TRIM()**函数的能力，这些能力对于普通开发人员来说可能并不明显。正如他所展示的那样，该函数接受一个语法中我从未见过的参数。 TRIM ( [ characters FROM ] string ) 如果您想自己尝试，请参考此SQL Fiddle示例。 - Ben

Answer 4

这里有一个你可以运行的例子：

我决定将结果转换为Xml值，这样当你点击它时，你就能够查看回车符。

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]

请记得删除 Cast-to-Xml，因为这只是一个概念验证以展示它的工作原理。

这种方法比当前被接受的答案更好在哪里呢？

乍一看，这似乎比已接受的答案使用了更多的函数。然而，事实并非如此。如果您将已接受的答案中列出的两种方法（去除前导和尾随空格）结合起来，您将不得不对记录进行两次更新，或者将所有逻辑复制到另一个逻辑中（任何地方都列出了@String），这将导致更多的函数调用并变得更加难以阅读。

- MikeTeeVee

---

为了删除所有这些字符（制表符、回车/换行符和空格），包括前导和尾随的，我们可以执行以下操作：SELECT CAST(SUBSTRING(@String, PATINDEX('%[^'+CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)+']%',@String), LEN(REPLACE(@String, ' ', '_')) - PATINDEX('%[^'+CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)+']%',@String) + 1 - PATINDEX('%[^'+CHAR(9)+CHAR(10)+CHAR(13)+CHAR(32)+']%', REVERSE(@String)) + 1) as Xml)[RemoveLeadingAndTrailingCRLFsOnly]。 - Matt Roy

Answer 5

要从预定义列表中修剪字符，您需要创建以下UDF（应在2008R2及以上版本中工作）。

一次处理两侧，不关心是CRLF、LFCR（是的，我见过那种可怕的东西不止一次）、裸LF还是一堆空格。

易于扩展，例如添加额外的参数以仅执行LTRIM/RTRIM，或进行完全清除（最后一部分在2017年通过合并STRING_AGG更简单，但在2008R2中完全可行）；事实上，这是我用来完成所有这些任务的某些内容的简化版本。如果有人感兴趣，请让我知道，我会更新：

CREATE FUNCTION fnTrimHarder
(
    @String VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN

    DECLARE
        @Start INT,
        @Len INT,
        @Chars CHAR(5) = CONCAT(
            CHAR(9), -- TAB
            CHAR(10), -- LF
            CHAR(13), -- CR
            ' '
        ), -- List of invalid characters
        @Return VARCHAR(MAX) = '';
    
    IF @String NOT LIKE '%[^' + @Chars + ']%' -- If string contains only invalid characters
    OR COALESCE(@String, '') = '' -- Optional addition for NULL handling
        RETURN @Return
    ELSE
    BEGIN -- Create a "table" of characters with ordinals, calculate the start of string and its length, then return the substring 
        WITH CTE AS (
            SELECT 1 AS n
            UNION ALL
            SELECT n + 1
            FROM CTE
            WHERE n < LEN(@String)
        )
        SELECT 
            @Start = MIN(n),
            @Len = 1 + MAX(n) - MIN(n)
        FROM CTE 
        WHERE SUBSTRING(@String, n, 1) NOT LIKE '[' + @Chars + ']';
        
        SET @Return = SUBSTRING(@String, @Start, @Len)
    END

    RETURN @Return

END
GO

- puszczyk

Answer 6

我被困在使用 Microsoft SQL Server 2008 R2 上，所以我基于 @sqluser 的 答案 来编写以下函数。如果字符串仅包含要修剪的字符，则此函数将返回空字符串。

让我感到困惑的是，PATINDEX 的模式必须包含在 % 字符之间，一度我认为它与 LIKE 语句中的相同通配符相同，但现在我相信这只是表示模式的语法，尽管我可能错了！

CREATE FUNCTION [dbo].[ExtendedLTRIM](@string_to_trim VARCHAR(MAX)) 
    RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @tab CHAR(1) = CHAR(9);
    DECLARE @line_feed CHAR(1) = CHAR(10);
    DECLARE @carriage_return CHAR(1) = CHAR(13);
    DECLARE @space CHAR(1) = CHAR(32);

    DECLARE @characters_to_trim VARCHAR(10)
    SET @characters_to_trim = @tab + @line_feed + @carriage_return + @space

    IF @string_to_trim LIKE '[' + @characters_to_trim + ']%'
    BEGIN
        DECLARE @first_non_trim_character INT = PATINDEX('%[^' + @characters_to_trim + ']%', @string_to_trim);

        IF @first_non_trim_character = 0 RETURN '';

        RETURN SUBSTRING(@string_to_trim, @first_non_trim_character, 8000)
    END

    RETURN @string_to_trim
END
GO

- Pseudonymous