SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Where to post my question (MS SQL)?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jimtimber
Yak Posting Veteran

United Kingdom
60 Posts

Posted - 06/16/2014 :  05:01:29  Show Profile  Reply with Quote
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?

Thanks

Jim

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 06/16/2014 :  07:02:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
What kind of problem are you experiencing?



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

jimtimber
Yak Posting Veteran

United Kingdom
60 Posts

Posted - 06/16/2014 :  10:54:14  Show Profile  Reply with Quote
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
Go to Top of Page

Ifor
Aged Yak Warrior

626 Posts

Posted - 06/16/2014 :  11:02:19  Show Profile  Reply with Quote

 SELECT COALESCE(title + ' ', '') + COALESCE(firstname + ' ', '') + COALESCE(surname, '') AS FullName
 FROM YourTable
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8768 Posts

Posted - 06/16/2014 :  11:06:09  Show Profile  Visit webfred's Homepage  Reply with Quote
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:
select
coalesce(titel, '')+' '+coalesce([first name],'')+' '+coalesce(surname,'') as MyColumn
from...

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.

Edited by - webfred on 06/16/2014 11:06:57
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 06/16/2014 :  11:42:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT CONCAT([First Name], ' ', [Last Name])



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

jimtimber
Yak Posting Veteran

United Kingdom
60 Posts

Posted - 06/16/2014 :  11:47:42  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000