Views with NULL values

Question

Post reply

Views with NULL values

KevD

Newbie

Points: 3
More actions
October 8, 2010 at 6:25 am

#145626

Hi,
I'm trying to create a view which will select a row from a table (that will always have only 1 record). Problem is that some fields will be NULL. The table is updated everyday and the NULL values can switch column.
I want to select all the values as 1 field, so concatenate plus add a newline after every value (with CHAR(13) + CHAR(10)). When I have a NULL value it also adds the newlines giving me empty lines which is not good.
I've tried with ISNULL(column, '') but an empty value is also not working properly.
Any1 could help me with this ?
Thanks in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

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

grahamc SSCertifiable Points: 6762 More actions · Answer 1

Try COALESCE(column, '')

otherwise can you post the CREATE VIEW statement

KevD Newbie Points: 3 More actions · Answer 2

Thanks for the reply. But works the same as ISNULL(), my newlines are still shown. My query in view is like this:

SELECT COALESCE (dZ_Field1, '') + CHAR(13) + CHAR(10) + COALESCE (dZ_Field2_A, '') + CHAR(13) + CHAR(10)

+ COALESCE (dZ_Field3_A, '') + CHAR(13) + CHAR(10) + COALESCE (dZ_Field3_A, '') + CHAR(13) + CHAR(10)

+ COALESCE (dZ_Field4_A, '') + CHAR(13) + CHAR(10) + COALESCE (dZ_Field5_A, '') AS ConcatString

FROM dbo.dZ_Data

So when 1 field is NULL it still adds the newlines, I should find a way to only concat the newlines if the field is not null. If the field is null it should just skip and not concat the newlines...

grahamc SSCertifiable Points: 6762 More actions · Answer 3

grahamc

SSCertifiable

Points: 6762

October 8, 2010 at 6:56 am

#1234214

and what does the data look like? sample?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Would this work?

COALESCE(MyCol + CHAR(13),'') + COALESCE(MyNextCol + CHAR(13,'') + ....

John

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

SELECT

COALESCE (dZ_Field1 + CHAR(13) + CHAR(10), '') +

COALESCE (dZ_Field2_A + CHAR(13) + CHAR(10), '') +

COALESCE (dZ_Field3_A + CHAR(13) + CHAR(10), '') +

COALESCE (dZ_Field4_A + CHAR(13) + CHAR(10), '') +

COALESCE (dZ_Field5_A, '') AS ConcatString

FROM dbo.dZ_Data

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

KevD Newbie Points: 3 More actions · Answer 6

KevD

Newbie

Points: 3

October 8, 2010 at 7:18 am

#1234238

This worked like a charm! Thanks alot.