sql脚本linux中去换行符,在SQL中替换换行符

我可能迟到了一年，但我的工作是查询＆amp; 每天都有MS-SQL，我厌倦了内置函数LTRIM()＆amp; RTRIM()(并且总是必须一起调用它们)，并且没有捕获最后有换行符的“脏”数据，因此我认为现在是实现更好的TRIM功能的时候了。 我欢迎同行反馈！

免责声明：这实际上删除(替换为单个空格)扩展形式的空格(制表符，换行符，回车符等)，因此它已从我原来的答案重命名为“CleanAndTrim”。 这里的想法是你的字符串里面不需要额外的特殊空白字符，因此如果它们不在头/尾发生，它们应该用普通空格替换。 如果您故意在字符串中存储此类字符(例如，您要将其运行的数据列)，请不要这样做！ 改进这个功能或编写你自己的字面意思只是从字符串的端点删除那些字符，而不是从'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配对的注释和解决方法，即用单个替换替换特定的char-pair。

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