如果你有一个问题，你只想删除尾随字符，你可以试试这个:

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

如果您的列数据类型是'text'，那么您将得到一个错误消息为

Msg 8116，级别16，状态1，第2行参数数据类型文本是 replace函数的参数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(@string, CHAR(13) + CHAR(10), '')

有时

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

不能工作。在这种情况下，使用以下片段代码:

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

我可能晚了一年，但我每天都在查询和MS-SQL上工作，我厌倦了内置函数LTRIM()和RTRIM()(并且总是必须一起调用它们)，并且不能捕获结尾有换行符的“脏”数据，所以我决定是时候实现一个更好的TRIM函数了。我欢迎同行的反馈!

Disclaimer: this actually removes (replaces with a single whitespace) extended forms of whitespace (tab, line-feed, carriage-return, etc.), so it's been renamed as "CleanAndTrim" from my original answer. The idea here is that your string doesn't need such extra special-whitespace characters inside it, and so if they don't occur at the head/tail, they should be replaced with a plain space. If you purposefully stored such characters in your string (say, your column of data that you're about to run this on), DON'T DO IT! Improve this function or write your own that literally just removes those characters from the endpoints of the string, not from the 'body'.

好了，现在免责声明更新了，下面是代码。

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