How to remove spaces in a row column

Question

I have a column nvarchar(2000)that can store long text below

There are many blank lines at the end of the text. Is there a way to remove these empty lines ( char(10))?

Use is replace(column,char(10),'')unacceptable. I do not want to spoil the content above. How to remove these char (10) only at the end of the text?

I am using SQL Server 2012.

Many thanks!!!

Answer 1

You can replace with CHAR(10)+CHAR(13)an 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'

You'll get:

--Before--
first row
second row
third row



--End
--After
first row
second row
third row
--End

There Beforeare 3 empty lines in the part

EDIT

, REPLACE RTRIM, :

select @text = RTRIM(REPLACE(@text,CHAR(32)+CHAR(13)+CHAR(10),''))

Answer 2

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

Answer 3

Thanks to fetching data from gofr1. This method will ONLY delete the tailor's lines. Tested, it works great 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

Answer 4

Use RTRIM and LTRIM. For selected query in SQL For example

select RTRIM (LTRIM ('j')) AS Column1

or If you want to use crop in your web form. You can use the data binding event record string using Str.Trim ();