Add 3 columns into another column

Question

Post reply

Add 3 columns into another column

Senthilkumar-215205

SSC Veteran

Points: 274
More actions
November 5, 2007 at 2:44 am

#70388

I have an existing table where i have add1, add2, add3 as columns. Now i want to add all the 3 into one column, Address. The problem is if any one column is NULL, then the result also becomes null.
Also, i want to introduce the [Enter] after add1 and add2 so that when i retrieve the Address, formatting looks good.
What i tried is set the formula for the Address column as [add1]+[add2]+[add3], but this gives Null when there is a null in any one of the column.
Can some one help me please?
kesk

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 1

try like:

selectisnull (add1, '') +isnull (add2, '') +isnull (add3, '') as 'Address' from

🙂

karthik M One Orange Chip Points: 29139 More actions · Answer 2

Just Update the Null columns as blank values

Update yourtablename

set add1 = ''

where add1 is null

Now execute your code, it should work

do You know why are you getting 'NULL' Values ?;)

karthik

goodguy SSCertifiable Points: 5456 More actions · Answer 3

[Quote]Also, i want to introduce the [Enter] after add1 and add2 so that when i retrieve the Address, formatting looks good.[/Quote]

SELECT ISNULL(add1,'') + CHAR(13) + CHAR(10) + ISNULL(add2,'') + CHAR(13) + CHAR(10) + ISNULL(add3,'')

Regards,

goodguy

Experience is a bad teacher whose exams precede its lessons

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 4

If you want to introduce line feed and carriage return between the lines of address you should go with Goodguy's solution. Else just using ISNULL(column,'') should do it.

Prasad Bhogadi
www.inforaise.com

BruceGwynne Valued Member Points: 54 More actions · Answer 5

The problem with goodguy's solution is that any blank fields will leave blank lines in the resulting select.

We faced a similar problem and got round it this way

DECLARE @AddressString VARCHAR(250)

SET @AddressString = ''

IF Address1 <> '' and Address1 IS NOT NULL

BEGIN

SET @AddressString = Address1 + CHAR(13) + CHAR(10)

END

IF Address2 <> '' and Address2 IS NOT NULL

BEGIN

SET @AddressString = @AddressString + Address2 + CHAR(13) + CHAR(10)

END

IF Address3 <> '' and Address3 IS NOT NULL

BEGIN

SET @AddressString = @AddressString + Address3 + CHAR(13) + CHAR(10)

END

And so on until all required address lines are included. Obviously you don't need a +CHAR(13) +CHAR(10) on the last line

Hope this helps

David McFarland SSChampion Points: 11815 More actions · Answer 6

While you can also do it pretty easily with CASE statements, you can also add a Replace function to goodguy's code and handle the blank lines in a single row, if you know the maximum number of address elements.

For three elements, like in the sample, the following should take care of your blank line issues:

DECLARE @CRLF char(2)

SET @CRLF = Char(13) + Char(10)

SELECT Replace(ISNULL(add1,'') + @CRLF + ISNULL(add2,'') + @CRLF + ISNULL(add3,''), @CRLF + @CRLF, @CRLF)

For four elements, you just add an extra replace like this, etc.

SELECT Replace(Replace(ISNULL(add1,'') + @CRLF + ISNULL(add2,'') + @CRLF + ISNULL(add3,'') + @CRLF + ISNULL(add4, ''), @CRLF + @CRLF, @CRLF), @CRLF + @CRLF, @CRLF)

brendt hess Hall of Fame Points: 3256 More actions · Answer 7

This is one of those cases where COALESCE makes for a fun alternative, because it can make your head hurt figuring out what it is doing. 😉 I am assuming a char(2) variable @CrLf with a Char(13) and a Char(10) in it

DECLARE @CrLf char(2)

SET @CrLf = Char(13) + Char(10)

SELECT COALESCE(Add1 + @CrLf, '') + COALESCE(Add2 + @CrLf, '') + COALESCE(Add3 + @CrLf, '') ....

If any one of the fields is NULL, then the field + @CrLf will be null, in which case an empty string will be appended. Otherwise, the AddN value plus an appended Cr/Lf pair will be appended.

Simple, no? And no REPLACE, no CASE.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 8

karthikeyan (11/5/2007)
Just Update the Null columns as blank values
Update yourtablename
set add1 = ''
where add1 is null
Now execute your code, it should work
do You know why are you getting 'NULL' Values ?;)

NULL is a valuable value in many cases... I don't recommend replacing NULLs with blanks...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 9

brendt hess (11/7/2007)
This is one of those cases where COALESCE makes for a fun alternative, because it can make your head hurt figuring out what it is doing. 😉 I am assuming a char(2) variable @CrLf with a Char(13) and a Char(10) in it
DECLARE @CrLf char(2)
SET @CrLf = Char(13) + Char(10)
SELECT COALESCE(Add1 + @CrLf, '') + COALESCE(Add2 + @CrLf, '') + COALESCE(Add3 + @CrLf, '') ....
If any one of the fields is NULL, then the field + @CrLf will be null, in which case an empty string will be appended. Otherwise, the AddN value plus an appended Cr/Lf pair will be appended.
Simple, no? And no REPLACE, no CASE.

ISNULL would also work here but I gotta say, "FINALLY!" Someone who understands how to deal with NULLs 😉 Nicely done...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BruceGwynne Valued Member Points: 54 More actions · Answer 10

Brendt's solution is elegant, but unfortunately won't work in our case without some amendment. Some of our fields correctly contain NULL, but some are just blank, and these leave blank lines in the selected address. It seems that the application is at fault as if you tab through an input field a blank results if no data is entered.

Adjusting Brendt's solution as follows gives just what we need

COALESCE((CASE WHEN a.Address1<>'' THEN a.Address1 ELSE NULL END) + @CrLf, '') +

COALESCE((CASE WHEN a.Address2<>'' THEN a.Address2 ELSE NULL END) + @CrLf, '') + ……..etc.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 11

You give up too soon 😉

[font="Courier New"]DECLARE @CrLf char(2)

SET @CrLf = Char(13) + Char(10)

SELECT COALESCE(NULLIF(Add1,' ') + @CrLf, '') + COALESCE(NULLIF(Add2,' ') + @CrLf, '') + COALESCE(NULLIF(Add3,' ') + @CrLf, '') ....[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BruceGwynne Valued Member Points: 54 More actions · Answer 12

Thanks Jeff

I overlooked NULLIF, not having used it much in the past

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 13

Heh... no problem there... most people don't even know it exists! 😛

Thanks for the feedback, Bruce.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)