concatenate fields and ignore nulls

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

concatenate fields and ignore nulls

Author

Topic

giovi2002
Starting Member

46 Posts

Posted - 2005-08-14 : 16:26:46

I would like to concatenate all my fields in the table per row after the insert into a field called 'mergefield'.

Example

fld1 fld2 fld 3 mergefield
aaa bb <null> aaabb
aa <null> ee aaee
etc
Problem is when using standard sql
select fld1+fld2+fld3 from table rows with one null field will disappear.
I've tried SET CONCAT_NULL_YIELDS_NULL { ON | OFF }
in my stored procedure but that didn't help.

Do you have an easy workaround without overcoding with if statements or case statements?

giovi2002
Starting Member

46 Posts

Posted - 2005-08-14 : 16:35:44

Oops think i found it.
Executed this in query analyzer
SET CONCAT_NULL_YIELDS_NULL OFF
select lijnnummer, send_key,convert(varchar(4),lijnnummer)+convert(varchar(4),send_key) As expr1 from aantal_dagen

First you see the two integers (lijnnummer, send)key) in the third column a varchar concatenation

1 1 11
1 1 11
2 1 21
1 1 11
1 1 11
3 NULL 3
4 NULL 4
5 NULL 5
6 NULL 6
7 NULL 7

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-14 : 17:48:45

There's also the isNull function

Be One with the Optimizer
TG

giovi2002
Starting Member

46 Posts

Posted - 2005-08-15 : 03:13:26

Yes the isnull function can be used with concatenation of two fields but not with three or more if i'm right. If one field would be null he will pick the first not nulled field instead of concatenation of the two not nulled fields

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2005-08-15 : 04:01:13

[code]
coalesce(column1,'') + coalesce(column2,'') + coalesce(column3,'')
[/code]

Don't use isnull, it is non-standard. Table have columns, not fields.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-15 : 08:25:07

>>Don't use isnull, it is non-standard

I've got no problem using sql server functions that aren't ansi-standards.

Coalesce and isNull have different functionalities. Coalesce can take multiple expressions and return the first non-null value. That is very usefull in some situations. IsNull only takes 2 expressions and supports a datatype precedence so that the second expression returns the same datatype that the first expression evaluates to. That is also very usefull (in other situations). Just make it your business to understand the benifits/limitationss of any functions you use.

mho

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources