在TSQL中替换换行符

Question

我想在TSQL字符串中替换（或删除）换行符。有什么建议吗？

显而易见的方法是使用字符串函数来替换或删除换行符。

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

就是不行...

- Peter

Answer 1

实际上，在SQL命令或脚本字符串中，换行符可以是CR、LF或CR+LF中的任何一种。要获取它们所有的话，需要像这样：

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

- RBarryYoung

---

只要您的字符串中只有一种换行符，那么这将起作用。但是SQL Server支持三种类型的换行符。事实上，如果您曾经提取过所有系统存储过程和脚本化视图，就会发现Microsoft自己使用了这三种类型的换行符。 - RBarryYoung

如果列数据类型为文本，则需要首先将其转换为nvarchar，然后执行替换操作：SELECT REPLACE(REPLACE(cast(@str as nvarchar(max)), CHAR(13), ''), CHAR(10), '')。 - akd

在我的C# WebApp中没有起作用，但是当我在SQL Manager中运行查询时，我发现列名为(无列名)，所以我在查询中添加了AS，现在它可以工作了。 REPLACE(REPLACE(MyColumn, CHAR(13), ''), CHAR(10), '') AS MyColumn - Slint

@Slint 是的，说得好。实际上，从客户端代码中使用它时，你应该添加一个列名。虽然很多时候你可以用 .columns[0] 代替。 - RBarryYoung

我正在使用的 Oracle 版本中，语句应该是 CHR 而不是 CHAR。以防有人试图从 Oracle 进行调试。否则，其他所有内容都适用。 - Sedona

Answer 2

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

- Mitch Wheat

---

这是我首先尝试的方法，但它不能可靠地处理所有数据。@RBarryYoung在上面说得对。 - Mark W Dickson

谢谢，我不得不稍微修改一下才能适应我的情况，对我来说是这样的：replace（replace（@string，char（13），''），char（10），''） - user734028

顺便提一下，在处理 nvarchar/nchar 字符串时，请使用 NCHAR(13) + NCHAR(10)，以避免在 n 和非 n 字符串类型之间进行可能昂贵的隐式转换。 - Dai

Answer 3

我可能晚了一年才加入这个派对，但我每天都在处理查询和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

- NateJ

---

过去的用户请注意更改和免责声明 - 对于最初关于使用和目的的假设，我深表歉意。 - NateJ

新更新！测试用例可以在这里找到：http://sqlfiddle.com/#!6/585a2/1/0 -- SQLFiddle似乎无法处理我的实际测试用例，因此我建立了一个“测试用例查询构建器”表，并提供了9个语句供您复制粘贴到自己的SSMS窗口中运行（当然，在创建模式之后，即函数和TestStrings表）。 - NateJ

微软非常喜欢你的TRIM函数，以至于他们将其包含在SQL Server 2017中 ;) - Geoff Griswald

不，你没有迟到：），感谢你提供的超级SQL函数。我在一个制表符和一个换行符之间加了一个空格，这个函数就起作用了。 - Dobin

Answer 4

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

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

- Cerebrus

---

这并不完全与被接受的答案相同；被接受的答案会移除任何{13, 10}组合，而这个方法只会移除具体的13和10的组合。对于Windows换行符来说没有影响，但其他编码可能会被忽略。 - Andrew Hill

Answer 5

为了做到大多数人想要的，创建一个不是实际换行符的占位符。然后你可以将以下方法结合起来使用：

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/>

如果想要更准确一些，那么第一种方法会比较合适。

- porkandcheese

---

我所做的是，用空格替换了换行符''。如果你有CR或LF，你只会得到一个空格，但如果你有CRLF，那么你就会得到两个空格。因此，我在它周围包裹了另一个replace，用单个空格替换双空格：REPLACE(REPLACE(REPLACE(ClientName, CHAR(13), ' '), CHAR(10), ' '),' ',' ')。 - Geoff Griswald

Answer 6

在SQL Server 2017及以后版本中，请使用函数。

了解更多。

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

1. IT 技术在开头和结尾进行修剪，不会在中间修剪
2. \r 和 \n 的顺序没有关系

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

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

- Rm558

Answer 7

如果您的列数据类型为“text”，那么您将会得到以下错误信息：

Msg 8116, Level 16, State 1, Line 2 Argument data type text is invalid for argument 1 of replace function.

在这种情况下，您需要将文本强制转换为nvarchar，然后再进行替换。

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

- akd

---

好的，这看起来很不错。现在，如果我要替换的是“!crlf ”，是的，那是一个空格字符在!crlf之后。问题是它出现在字符串的中间，我能用以下代码吗：SELECT REPLACE(REPLACE(cast(@str as nvarchar(MAX)), CHAR(33), CHAR(13), CHAR(10), CHAR(32), '')或者我写错了吗？字符串看起来像这样：allow remote attackers to bypass a sandbox protection mechanism and gain privileges via a crafted web site t! hat is accessed with Internet Explorer, 重点关注单词t! hat...那是我的麻烦点。 - bbcompent1

Answer 8

有时候。

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

无法工作。在这种情况下，请使用以下代码片段：

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

- Robin

Answer 9

如果你只想删除字符末尾的内容，可以尝试以下方法：

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报告的空间，我把它们缩小了。

- DaveX

Answer 10

我想要对一列的内容进行清理以生成CSV文件，因此需要去除varchar内部的逗号（，）以及换行和回车符。

我还希望最终使用生成的CSV文件创建另一个脚本（插入到另一个数据库中的行），因此需要将varchar内的'更改为''，最终得到如下结果...

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

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

- andrew pate