Declare @String nvarchar(2000) = 'Row 1 with some text
Row 2 with some other text
'
Select reverse(substring(Reverse(@String),patindex('%[0-z]%',Reverse(@String)),2000))+char(13)+char(10)
Row 1 with some text
Row 2 with some other text
CHAR(10)+CHAR(13)
替换为空字符串:declare @text nvarchar(max) =
'first row
second row
third row
'
print '--Before--'
print @text
print '--End'
select @text = REPLACE(@text,CHAR(10)+CHAR(13),'') --10 and 13, not 13 and 10!
print '--After'
print @text
print '--End'
我会为你提供:
--Before--
first row
second row
third row
--End
--After
first row
second row
third row
--End
在Before
部分有3行空白行。
编辑
如果每个空白行都有一个或多个空格,则更改REPLACE语句中的字符并添加RTRIM以去除剩余的空格:
select @text = RTRIM(REPLACE(@text,CHAR(32)+CHAR(13)+CHAR(10),''))
declare @text nvarchar(max) =
'first row
second row
third row
'
print '--Before--'
print @text
print '--End'
--It will delete only the tailor lines.
set @text = reverse(stuff(reverse(@text),1,patindex('%'+char(13)+'[^'+char(10)+']%',reverse(@text)),''))
print '--After'
print @text
print '--End'
Result