Nulls - 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)

Nulls

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-10-09 : 19:03:18

I have written a select query when executes in sqlserver 2005 is returning the result with nulls when there is no data for that particular record.
Whereas if i run the same select query in sqlserver 2000 i m getting the desired results for that paricular record where there is no data i got the empty.

Data type is char
Example:

Sqlserver2000
-------------

empid
-----
01
03
04

08

Sqlserver2005
-------------

empid
-----
01
03
04
Null
08

How to get the empty instead null in sqlserver 2005.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-10-09 : 19:20:35

coalesce(empid,'')

or

isnull(empid,'')

Kristen
Test

22859 Posts

Posted - 2007-10-09 : 22:29:57

Need to see the SQL for the query pls

I wouldn't fix it using COALESCE / ISNULL, I would want to find the cause

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-10 : 02:07:04

Yes. Post the query you used. Let us see if there are some more strange issues between 2000 and 2005

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-10 : 02:15:43

What is your SET ANSI_NULL setting?

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-10 : 04:13:24

"Let us see if there are some more strange issues between 2000 and 2005"

That's the part that scared me too!

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-10-10 : 11:04:27

Select
CONVERT(char(3),table.FIPS)
from
table

Kristen
Test

22859 Posts

Posted - 2007-10-10 : 13:14:37

[code]
CREATE TABLE T_20071010
(
FIPS int
)
GO
INSERT INTO T_20071010
SELECT 123 UNION ALL
SELECT NULL

SELECT CONVERT(char(3), T_20071010.FIPS)
FROM T_20071010
GO
DROP TABLE T_20071010
GO

SQL 2000
========
123
NULL

SQL 2005
========
123
NULL
[/code]
So it isn't as simple as just the CHAR conversion ...

Anything "special" about the FIPS column in your table?

Kristen

Kristen
Test

22859 Posts

Posted - 2007-10-10 : 13:21:51

I can't find any combination of ANSI_NULL_DEFAULT/ANSI_NULL_DFLT_ON, ANSI_NULLS, ANSI_PADDING, CONCAT_NULL_YIELDS_NULL which yields a silent conversion to "blank"

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources