I have a nvarchar(2000) column which may store a long text as below
There are a lot of blank rows at end of text. Is there a way to delete these blank rows (char(10))?
Using replace(column,char(10),'') is not acceptable. I don't want to mess up the content above. How to delete these char(10) only at the end of text?
I'm using SQL Server 2012.
Many thanks!!!
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)
Returns
Row 1 with some text
Row 2 with some other text
You can replace CHAR(10)+CHAR(13) with empty string:
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'
Will give you:
--Before--
first row
second row
third row
--End
--After
first row
second row
third row
--End
In Before part there are 3 empty rows
EDIT
If each empty row got a space(s) then change chars in REPLACE statement and add RTRIM to cut off spaces left:
select @text = RTRIM(REPLACE(@text,CHAR(32)+CHAR(13)+CHAR(10),''))
Thanks to the sample data from gofr1. This method will ONLY delete the tailor lines. Tested, it works perfect in SSMS. :)
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
Use RTRIM and LTRIM For your select query in SQL For example
select RTRIM ( LTRIM ( ' j ')) AS Column1
or If you want use trim on your web form.You can use databound event write string using Str.Trim();
