Geting null - 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 2008 Forums

Transact-SQL (2008)

Geting null

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2010-12-29 : 09:49:30

When I do the following SQL I get a record.
SELECT FirstName, MiddleName, LastName
FROM dbo.OffenderAliases
WHERE (OffenderID = 14419)

Record.....
Tom NULL Smith

This sql brings back Null. I need this to work
SELECT CASE
WHEN firstname = '' THEN ''
ELSE firstname + ' '
END + CASE
WHEN middlename = '' THEN ''
ELSE middlename + ' '
END + CASE
WHEN lastname = '' THEN ''
ELSE lastname + ' '
END AS alias_name
FROM dbo.offenderaliases
WHERE ( offenderid = 14419 )

Dave
Helixpoint Web Development
http://www.helixpoint.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-29 : 09:56:37

[code]
SELECT COALESCE(firstname + ' ','') + COALESCE(middlename + ' ','') + COALESCE(lastname + ' ','') AS alias_name
FROM dbo.offenderaliases
WHERE ( offenderid = 14419 )[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2010-12-29 : 10:00:12

Great. Thank you

Dave
Helixpoint Web Development
http://www.helixpoint.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-29 : 10:00:58

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-12-29 : 15:31:12

You can COALESCE the NULL to an empty string, but a better way is to use DDL to keep bad data out in the first place.

middle_name VARCHAR(20) NOT NULL DEFAULT ''
CHECK (middle_name = RTRIM(LTRIM (middle_name))

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources