November 5, 2007 at 2:44 am
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
November 5, 2007 at 3:06 am
try like:
select isnull (add1, '') + isnull (add2, '') + isnull (add3, '') as 'Address' from
🙂
November 5, 2007 at 6:30 am
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
November 5, 2007 at 7:24 am
[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,'')
November 6, 2007 at 7:01 am
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
November 7, 2007 at 8:16 am
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
November 7, 2007 at 2:34 pm
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)
November 7, 2007 at 3:34 pm
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.
November 7, 2007 at 4:16 pm
karthikeyan (11/5/2007)
Just Update the Null columns as blank valuesUpdate 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
November 7, 2007 at 4:19 pm
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 itDECLARE @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
November 8, 2007 at 2:31 am
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.
November 8, 2007 at 10:37 pm
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
November 9, 2007 at 1:29 am
Thanks Jeff
I overlooked NULLIF, not having used it much in the past
November 9, 2007 at 5:13 pm
Heh... no problem there... most people don't even know it exists! 😛
Thanks for the feedback, Bruce.
--Jeff Moden
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply