CODEWITHSUNDEEP
sql-serverstringt-sqlreplacesql-server-2012
Joe Guan
Joe Guan

Reputation: 41

How to delete blank spaces on a string column

I have a nvarchar(2000) column which may store a long text as below

blank rows after text

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!!!

Upvotes: 4

Views: 1829

Answers (4)

gofr1
gofr1

Reputation: 15977

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),''))

Upvotes: 2

Dance-Henry
Dance-Henry

Reputation: 953

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

enter image description here

Upvotes: 1

Shyam Sa
Shyam Sa

Reputation: 331

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();

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81930

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

Upvotes: 2

Related Questions