CODEWITHSUNDEEP
sql-serverreplaceformatting
Janek
Janek

Reputation: 85

How to remove fully blank lines from text field?

I've got a data imported from mail messages to a table and sometimes it looks like that:

Line 1

Line 2



Line 3

What I’d like is for it to look like this:

Line 1
Line 2
Line 3

Is there any option how to achieve this? I only need to replace fully blank lines to have it presented in a Report Builder with much convenient way.

Upvotes: 2

Views: 7683

Answers (1)

t-clausen.dk
t-clausen.dk

Reputation: 44326

Assuming your text field is varchar, since noone uses text anymore.

A line change is in most cases ascii 13 + ascii 10. By replacing all ascii 10 followed by ascii 13, you will remove the blank lines (Unless they contain spaces).

DECLARE @x varchar(max) =
'Line 1

Line 2


Line 3'

SELECT replace(@x, char(10) + char(13), '')

Result:

Line 1
Line 2
Line 3

Proof that this also works on text:

create table x(x text)
insert x values(
'LINE1

LINE2


LINE3
')

SELECT replace(cast(x as varchar(max)), char(10) + char(13), '')
FROM x

Result:

LINE1
LINE2
LINE3

Upvotes: 3

Related Questions