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.
Author |
Topic |
jimtimber
Yak Posting Veteran
60 Posts |
Posted - 2014-06-16 : 05:01:29
|
HI,I'm new to SQL and am having an issue with an MS-SQL query. Is there a particular part of the forum I should post this please?ThanksJim |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-06-16 : 07:02:52
|
What kind of problem are you experiencing? Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
jimtimber
Yak Posting Veteran
60 Posts |
Posted - 2014-06-16 : 10:54:14
|
I'm trying to merge "title" + "first name" and "surname" but if the title is blank on the database, it doesn't pull through the other name parts, even though they are in the record.Jim |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2014-06-16 : 11:02:19
|
[code] SELECT COALESCE(title + ' ', '') + COALESCE(firstname + ' ', '') + COALESCE(surname, '') AS FullName FROM YourTable [/code] |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2014-06-16 : 11:06:09
|
This is the known behavior in most SQL Server configurations.If you try to concatenate strings and there is at least 1 NULL value involved then the result is NULL.You can do this for example:selectcoalesce(titel, '')+' '+coalesce([first name],'')+' '+coalesce(surname,'') as MyColumnfrom...In this solution coalesce() will replace a given NULL value in a column by an empty string (2 single quotes '') so the concatenation will return what you have expected... Too old to Rock'n'Roll too young to die. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-06-16 : 11:42:39
|
SELECT CONCAT([First Name], ' ', [Last Name]) Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
jimtimber
Yak Posting Veteran
60 Posts |
Posted - 2014-06-16 : 11:47:42
|
Thank you both! The COALESCE idea has worked. You've saved me a lot of stressing :) I'm new to this SQL language so please bear with me :)Jim |
|
|
|
|
|
|
|