Convert NULL to something? - 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 2000 Forums

Transact-SQL (2000)

Convert NULL to something?

Author

Topic

NiceSituation
Starting Member

22 Posts

Posted - 2008-03-27 : 01:32:14

Hi everyone, I have a view in which I want to convert cells with value <NULL> to some value (in this case '0000'). I have tried many ways but have been unsuccessful. Here is what I have right now:

SELECT
Numbers, Orders,
SchedKey = CASE SchedKey
WHEN NULL THEN '0000'
WHEN '' THEN '0000'
WHEN ' ' THEN '0000'
WHEN '<NULL>' THEN '0000'
ELSE SchedKey
END
FROM My_Table

As you can see I tried different approaches (if I remove the colons from '<NULL>' I get a syntax error) but they did not fully work, because although some values indeed appear as '0000' (perhaps when SchedKey is '' or ' '), most that have nothing appear as <NULL>.

Any thoughts?

May today be good, and tomorrow better

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-27 : 01:54:29

[code]case
when SchedKey is null
then '0000'
when SchedKey in ('',' ')
then '0000'
else SchedKey
end[/code]

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-27 : 02:20:10

SELECT COALESCE(NULLIF(SchedKey, ''), '0000')

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

NiceSituation
Starting Member

22 Posts

Posted - 2008-03-27 : 12:22:15

Thank you both for your help! My problem is solved now. One more thing though: if I use a "CASE", is there a way to give the column "Schedkey" another name for display in the results table (it seems that you cannot use the usual "AS Another_Name" since it returns an error)?

May today be good, and tomorrow better

ocary
Starting Member

12 Posts

Posted - 2008-03-27 : 12:44:52

Add the display name and an equals sign before the case keyword. (Just as you had in your original post).

EG:
displayname = case
when SchedKey is null
the....

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-27 : 13:39:53

Or for ANSI compliance: