T-SQL Address line Packing

Pascal Ganaye

I am trying to pack four address lines, to leave no blank between the lines and no null values. The code below is so horrid, it could qualify quite well to the coding horror forum. This is also a question so I post it here. The question is : - How would you write this more elegantly ? It would be fun to compare various other ways, I will perhaps post some

DECLARE @line1 AS VARCHAR(255)
DECLARE @line2 AS VARCHAR(255)
DECLARE @line3 AS VARCHAR(255)
DECLARE @line4 AS VARCHAR(255)

SET @line1 = '12 orchard st '
SET @line2 = NULL
SET @line3 = ' '
SET @line4 = 'BATH'

SET @line1 = RTRIM(LTRIM(COALESCE(@line1,'')))
SET @line2 = RTRIM(LTRIM(COALESCE(@line2,'')))
SET @line3 = RTRIM(LTRIM(COALESCE(@line3,'')))
SET @line4 = RTRIM(LTRIM(COALESCE(@line4,'')))

DECLARE @cpt INTEGER

WHILE @line1 = '' AND @cpt<3
BEGIN
SET @line1 = @line2
SET @line2 = @line3
SET @line3 = @line4
SET @line4 = ''
SET @cpt = @cpt + 1
END

SET @cpt = 0
WHILE @line2 = '' AND @cpt<2
BEGIN
SET @line2 = @line3
SET @line3 = @line4
SET @line4 = ''
SET @cpt = @cpt + 1
END

IF @line3 = ''
BEGIN
SET @line3 = @line4
SET @line4 = ''
END

PRINT 'Line 1:' + @line1
PRINT 'Line 2:' + @line2
PRINT 'Line 3:' + @line3
PRINT 'Line 4:' + @line4

Goutam Patra

If you want to just make a single string then there is a solution

DECLARE @line1 AS VARCHAR(255)
DECLARE @line2 AS VARCHAR(255)
DECLARE @line3 AS VARCHAR(255)
DECLARE @line4 AS VARCHAR(255)
DECLARE @line5 AS VARCHAR(2000)

SET @line1 = '12 orchard st '
SET @line2 = NULL
SET @line3 = ' '
SET @line4 = 'BATH'

SET @line5 = (SELECT RTRIM(LTRIM(COALESCE(@line1,''))) +
CASE WHEN LEN(RTRIM(LTRIM(COALESCE(@line2,'')))) > 0 THEN CHAR(13) ELSE '' END + RTRIM(LTRIM(COALESCE(@line2,''))) +
CASE WHEN LEN(RTRIM(LTRIM(COALESCE(@line3,'')))) > 0 THEN CHAR(13) ELSE '' END + RTRIM(LTRIM(COALESCE(@line3,''))) +
CASE WHEN LEN(RTRIM(LTRIM(COALESCE(@line4,'')))) > 0 THEN CHAR(13) ELSE '' END + RTRIM(LTRIM(COALESCE(@line4,''))))

PRINT @line5

Pascal Ganaye

Then you would need SET @line1 = SUBSTRING(line5, [something here], [something here]) SET @line2 = SUBSTRING(line5, [something here], [something here]) SET @line3 = SUBSTRING(line5, [something here], [something here]) SET @line4 = SUBSTRING(line5, [something here], [something here]) Wouldn't you?

Goutam Patra

Well, thats why i said in my post If you want it in a single string. and then this will not solve your purpose

Mycroft Holmes

Why do you insist on 4 variables, printing Patras solution will achieve the same output

Never underestimate the power of human stupidity RAH

Luc Pattyn

a bubble-sort approach could solve that; in pseudo-code:

do {
bool more=false;
if (line1==empty && line2!=empty) {line1=line2; line2=empty; more=true;}
if (line2==empty && line3!=empty) {line2=line3; line3=empty; more=true;}
if (line3==empty && line4!=empty) {line3=line4; line4=empty; more=true;}
while(more);

:)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.