替换 TSQL 中的换行符

实际上，SQL 命令或脚本字符串中的新行可以是 CR、LF 或 CR+LF 中的任何一个。为了得到它们，你需要这样的东西：

SELECT REPLACE(REPLACE(@str, CHAR(13), ''), CHAR(10), '')

---

REPLACE(@string, CHAR(13) + CHAR(10), '')

---

我可能迟到了一年，但我每天都在处理查询和 MS-SQL，我厌倦了内置函数 LTRIM() 和 RTRIM()（并且总是不得不一起调用它们），并且没有捕捉到最后有换行符的“脏”数据，所以我决定是时候实现更好的 TRIM 函数了。我会欢迎同行的反馈！

免责声明：这实际上删除（替换为单个空格）扩展形式的空格（制表符、换行符、回车符等），因此从我的原始答案中将其重命名为“CleanAndTrim”。这里的想法是您的字符串不需要在其中包含这些额外的特殊空白字符，因此如果它们没有出现在头部/尾部，则应该将它们替换为纯空格。如果您故意将此类字符存储在字符串中（例如，您将要运行它的数据列），请不要这样做！改进此函数或编写您自己的函数，从字面上删除字符串端点中的这些字符，而不是从“正文”中删除。

好的，现在免责声明已更新，这是代码。

-- =============================================
-- Description: TRIMs a string 'for real' - removes standard whitespace from ends,
-- and replaces ASCII-char's 9-13, which are tab, line-feed, vert tab,
-- form-feed, & carriage-return (respectively), with a whitespace
-- (and then trims that off if it's still at the beginning or end, of course).
-- =============================================
CREATE FUNCTION [fn_CleanAndTrim] (
       @Str nvarchar(max)
)
RETURNS nvarchar(max) AS
BEGIN
       DECLARE @Result nvarchar(max)

       SET @Result = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
              LTRIM(RTRIM(@Str)), CHAR(9), ' '), CHAR(10), ' '), CHAR(11), ' '), CHAR(12), ' '), CHAR(13), ' ')))

       RETURN @Result
END

干杯!

另一个免责声明：您的典型 Windows 换行符是 CR+LF，因此如果您的字符串包含这些，您最终会用“双”空格替换它们。

2016 年更新：新版本让您可以选择用您选择的其他字符替换这些特殊空白字符！这还包括评论和 Windows CR+LF 配对的解决方法，即用单个替换替换特定的字符对。

IF OBJECT_ID('dbo.fn_CleanAndTrim') IS NULL
    EXEC ('CREATE FUNCTION dbo.fn_CleanAndTrim () RETURNS INT AS BEGIN RETURN 0 END')
GO
-- =============================================
-- Author: Nate Johnson
-- Source: http://stackoverflow.com/posts/24068265
-- Description: TRIMs a string 'for real' - removes standard whitespace from ends,
-- and replaces ASCII-char's 9-13, which are tab, line-feed, vert tab, form-feed,
-- & carriage-return (respectively), with a whitespace or specified character(s).
-- Option "@PurgeReplaceCharsAtEnds" determines whether or not to remove extra head/tail
-- replacement-chars from the string after doing the initial replacements.
-- This is only truly useful if you're replacing the special-chars with something
-- **OTHER** than a space, because plain LTRIM/RTRIM will have already removed those.
-- =============================================
ALTER FUNCTION dbo.[fn_CleanAndTrim] (
    @Str NVARCHAR(MAX)
    , @ReplaceTabWith NVARCHAR(5) = ' '
    , @ReplaceNewlineWith NVARCHAR(5) = ' '
    , @PurgeReplaceCharsAtEnds BIT = 1
)
RETURNS NVARCHAR(MAX) AS
BEGIN
    DECLARE @Result NVARCHAR(MAX)

    --The main work (trim & initial replacements)
    SET @Result = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        LTRIM(RTRIM(@Str))  --Basic trim
        , NCHAR(9), @ReplaceTabWith), NCHAR(11), @ReplaceTabWith)   --Replace tab & vertical-tab
        , (NCHAR(13) + NCHAR(10)), @ReplaceNewlineWith) --Replace "Windows" linebreak (CR+LF)
        , NCHAR(10), @ReplaceNewlineWith), NCHAR(12), @ReplaceNewlineWith), NCHAR(13), @ReplaceNewlineWith)))   --Replace other newlines

    --If asked to trim replacement-char's from the ends & they're not both whitespaces
    IF (@PurgeReplaceCharsAtEnds = 1 AND NOT (@ReplaceTabWith = N' ' AND @ReplaceNewlineWith = N' '))
    BEGIN
        --Purge from head of string (beginning)
        WHILE (LEFT(@Result, DATALENGTH(@ReplaceTabWith)/2) = @ReplaceTabWith)
            SET @Result = SUBSTRING(@Result, DATALENGTH(@ReplaceTabWith)/2 + 1, DATALENGTH(@Result)/2)

        WHILE (LEFT(@Result, DATALENGTH(@ReplaceNewlineWith)/2) = @ReplaceNewlineWith)
            SET @Result = SUBSTRING(@Result, DATALENGTH(@ReplaceNewlineWith)/2 + 1, DATALENGTH(@Result)/2)

        --Purge from tail of string (end)
        WHILE (RIGHT(@Result, DATALENGTH(@ReplaceTabWith)/2) = @ReplaceTabWith)
            SET @Result = SUBSTRING(@Result, 1, DATALENGTH(@Result)/2 - DATALENGTH(@ReplaceTabWith)/2)

        WHILE (RIGHT(@Result, DATALENGTH(@ReplaceNewlineWith)/2) = @ReplaceNewlineWith)
            SET @Result = SUBSTRING(@Result, 1, DATALENGTH(@Result)/2 - DATALENGTH(@ReplaceNewlineWith)/2)
    END

    RETURN @Result
END
GO

---

T-SQL 中的换行由 CHAR(13) & CHAR(10) 表示（回车 + 换行）。因此，您可以使用要替换换行符的文本创建一个 REPLACE 语句。

REPLACE(MyField, CHAR(13) + CHAR(10), 'something else')

---

要执行大多数人想要的操作，请创建一个不是实际换行符的占位符。然后，您实际上可以结合以下方法：

REPLACE(REPLACE(REPLACE(MyField, CHAR(13) + CHAR(10), 'something else'), CHAR(13), 'something else'), CHAR(10), 'something else')

这样您只需更换一次。方法：

REPLACE(REPLACE(MyField, CHAR(13), ''), CHAR(10), '')

如果您只想摆脱 CRLF 字符，但如果您想要一个占位符，例如

<br/>

什么的，那么第一种方法更准确一点。

---

如果您的列数据类型是“文本”，那么您将收到一条错误消息

消息 8116，级别 16，状态 1，第 2 行 参数数据类型文本对于替换函数的参数 1 无效。

在这种情况下，您需要将文本转换为 nvarchar 然后替换

SELECT REPLACE(REPLACE(cast(@str as nvarchar(max)), CHAR(13), ''), CHAR(10), '')

---

在 SQL Server 2017 &稍后，使用 Trim

Select Trim(char(10) + char(13) from @str)

它在开始和结束时修剪，而不是在中间 \r 和 \n 的顺序无关紧要

我用它来修剪文件名的特殊字符

Select Trim(char(10) + char(13) + ' *<>' from @fileName)

---

有时

REPLACE(myString, CHAR(13) + CHAR(10), ' ')

不会工作。在这种情况下，请使用以下代码段：

REPLACE(REPLACE(myString, CHAR(13),''), CHAR(10), ' ')

---

如果您遇到只想删除尾随字符的问题，可以尝试以下操作：

WHILE EXISTS
(SELECT * FROM @ReportSet WHERE
    ASCII(right(addr_3,1)) = 10
    OR ASCII(right(addr_3,1)) = 13
    OR ASCII(right(addr_3,1)) = 32)
BEGIN
    UPDATE @ReportSet
    SET addr_3 = LEFT(addr_3,LEN(addr_3)-1)
    WHERE 
    ASCII(right(addr_3,1)) = 10
    OR ASCII(right(addr_3,1)) = 13
    OR ASCII(right(addr_3,1)) = 32
END

这解决了我遇到的地址问题，其中程序创建了具有固定行数的字段，即使这些行是空的。为了节省 SSRS 报告中的空间，我将它们删减了。

---

如果您有使用 sp_helptext 的打开过程，则只需复制新 sql 查询中的所有文本并按 ctrl+h 按钮使用正则表达式替换并将 ^\n 放入查找字段替换为空白。有关更多详细信息，请查看图片。enter image description here

---

@Cerebrus 解决方案：不支持字符串“+”的 H2。所以：

REPLACE(string, CHAR(13) || CHAR(10), 'replacementString')

---

我想清理列的内容以生成 csv 文件，所以想去掉 varchar 中的逗号 (,) 以及换行符和回车符。

我还想最终使用生成的 csv 创建另一个脚本（将行插入另一个数据库），因此还需要将 varchar 中的 ' 更改为 '' 所以最终得到了这个......

REPLACE(REPLACE(REPLACE(REPLACE(ErrorMessage, CHAR(13), ''), CHAR(10), ''),',',''),'''','''''')

可能还有其他更好的方法，但它完成了工作。

---

上面/之前发布的答案被报告为替换 CHAR(13)CHAR(10) 回车：

REPLACE(REPLACE(REPLACE(MyField, CHAR(13) + CHAR(10), 'something else'), CHAR(13), 'something else'), CHAR(10), 'something else')

永远不会到达代码的 REPLACE(MyField, CHAR(13) + CHAR(10), 'something else') 部分，并将返回不需要的结果：

'something else''something else'

而不是一个单一的预期结果：

'something else'

这将需要这样重写 REPLACE 脚本：

REPLACE(REPLACE(REPLACE(MyField, CHAR(10), 'something else'), CHAR(13), 'something else'), CHAR(13) + CHAR(10), 'something else')

由于流程首先测试第一个/最左的 REPLACE 语句，然后在失败时将继续测试下一个 REPLACE 语句。

---