Concatenating fields - SQL Server Forums

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)

Concatenating fields

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-11-01 : 07:53:40

I have this

SELECT vehicleref,manufacturer + ' ' + model + ' ' + derivative + ' ' + additionalfreetext AS Vehicle FROM table

This works fine unless additionalfree text is NULL, then it returns NULL. Can I make it so that if any of the 4 fields are null then it just concatenates the remaining ones

Thanks

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-11-01 : 07:58:03

look at coalesce in BOL

coalesce(field,'')

Em

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-11-01 : 08:57:28

Hi,

Cant get this to work, if I use

SELECT vehicleref,COALESCE(manufacturer,' ', model,' ',derivative,' ',additionalfreetext) AS Vehicle FROM table

It just returns the manufacturer.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-01 : 09:05:21

You need to apply to all the columns

SELECT vehicleref,coalesce(manufacturer,'') + ' ' + coalesce(model,'') + ' ' + coalesce(derivative,'') + ' ' + coalesce(additionalfreetext,'') AS Vehicle FROM table

Madhivanan

Failing to plan is Planning to fail

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-11-01 : 09:15:13

Got it!

Cheers

JBelthoff
Posting Yak Master

173 Posts

Posted - 2007-11-01 : 15:51:17

Just curios, but wouldn't IsNull() work better here?


Select 
	IsNull(vehicleref, '')
	,IsNull(manufacturer,"") 
	+ ' ' 
	+ IsNull(model, '') 
	+ ' ' 
	+ IsNull(derivative, '') 
	+ ' ' 
	+ IsNull(additionalfreetext, '') 
As Vehicle 
From table

JBelthoff
• Hosts Station is a Professional Asp Hosting Provider
• Position SEO can provide your company with SEO Services at an affordable price
› As far as myself... I do this for fun!

Kristen
Test

22859 Posts

Posted - 2007-11-01 : 16:23:03

"Just curios, but wouldn't IsNull() work better here?"

Same thing but:

IsNull takes only 2 parameters, COALESCE takes unlimited number of parameter
COALESCE is standards-compliant
IsNull has some unexpected side effects if datatypes of the parameters are different

and finally

IsNull is a rubbish name for something that returns the first NON-null parameter! Although I have to admit that COALESCE is not really a word in common-English-parlance

Note that


coalesce(manufacturer,'') + ' ' + coalesce(model,'') + ' '

is liable to give you multiple adjacent spaces if a parameter is missing, so you might want to do:


coalesce(manufacturer + ' ', '') + coalesce(model + ' ','') + ...

instead

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources