java字符串 sql换行符_替换TSQL中的换行符

我可能晚了一年，但我每天都在处理查询和MS-SQL，我厌倦了内置函数LTRIM()和RTRIM()(并且总是要一起调用它们)，以及没有 grab 最后有新行的'脏'数据，所以我认为现在是实现更好的TRIM功能的时候了 . 我欢迎同行反馈！

免责声明：这实际上是 removes (替换为单个空格)扩展形式的空白(制表符，换行符，回车符等)，因此它内部需要这些额外的特殊空格字符，所以如果它们不在'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'重新开始运行这个)，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，所以如果你的字符串包含那些，你最终会用"double"空格替换它们 .

UPDATE, 2016 ：一个新版本，可让您选择用您选择的 other 个字符替换这些特殊空白字符！这还包括对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