Table query problem - 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

General SQL Server Forums

New to SQL Server Programming

Table query problem

Author

Topic

macca
Posting Yak Master

146 Posts

Posted - 2009-08-05 : 06:31:28

I am querying a table and selecting the names on the records.
The problem I have is that some of the Fname's are blank and this then returns a NULL record even though the Sname has a value.
Anyone know how to solve this?

My code is below:

SELECT Fname + ' ' + Sname AS Name, Id

FROM NameTable
ORDER BY Name

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-05 : 06:51:44

use ISNULL or COALESCE

Example:


DECLARE @foo TABLE (
	[fname] VARCHAR(255)
	, [sname] VARCHAR(255)
	)
INSERT @foo
      SELECT 'Fred', 'Flintstone'
UNION SELECT NULL, 'Rubble'

SELECT
	[fname] + ' ' + [sname] AS [Name]
FROM
	@foo

SELECT
	ISNULL([fname], '') + ' ' + [sname] AS [Name]
FROM
	@foo

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

EricLapine
Starting Member

1 Post

Posted - 2009-08-05 : 06:52:56

Concatenating a NULL value to anything returns a NULL value.
Replace the first line of code with the following:

SELECT ISNULL(Fname,'') + ' ' + Sname AS Name, Id

Here, the ISNULL function will replace a NULL value with an empty string.

macca
Posting Yak Master

146 Posts

Posted - 2009-08-05 : 06:58:38

Thanks Eric and Charlie.
Those worked for me.

macca

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-08-05 : 08:25:09

Might want to wrap Sname in an ISNULL also.

Or look at CONCAT_NULL_YIELDS_NULL

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources