concatenate with null turning null

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 2005 Forums

Transact-SQL (2005)

concatenate with null turning null

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-27 : 06:55:29

when i do

select productid,title+' '+catalognumber as name2 from qryproducts order by title

if catalognumber is null then it returns null -- I want it to always return the name and if there is a catalog number then also the catalog number

nr
SQLTeam MVY

12543 Posts

Posted - 2007-03-27 : 07:01:15

select productid, title + coalesce(' ' + catalognumber,'') as name2 from qryproducts order by title

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-27 : 07:06:17

[code]set CONCAT_NULL_YIELDS_NULL on
go

select null + 'abc'

set CONCAT_NULL_YIELDS_NULL off
go

select null + 'abc'[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-27 : 07:07:55

select productid, title + coalesce(' ' + catalognumber, '') as name2 from qryproducts order by title

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-27 : 10:55:18

Or you can do

select productid, title + coalesce(' ' + catalognumber, '') as name2 from qryproducts order by title