January 22, 2002 at 7:35 am
I'd like to know if there is a T-SQL command to replace NULL values by empty (or an other value) for all columns in the result of a query (without using ISNULL).
Thanks for anyone who can help me.
January 22, 2002 at 7:56 am
This is the primary purpose for ISNULL, to replace a NULL value with some other value. Is there a restriction which prevents you from using it?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 22, 2002 at 8:37 am
Thank you for your answer. My problem is that I have an old application and sometimes the Null Values are converted as 'NULL' characters (the interface with SQL/Sis not very good)!
So, I can change all columns in each select order with ISNULL if there is no other command (server or global parameter ??).
Thank you if you have any information about that.
January 22, 2002 at 9:35 am
Yes, you would select isnull( column_name, '') replacing column_name iwth the appropriate column. Replace '' with whatever value you want returned ('NULL', 0, etc.).
Steve Jones
January 22, 2002 at 10:15 am
Steve,
My understanding of ISNULL; The value you want returned must be of the same datatype you are wanting to swap out the null with.
example. If the NULL Column is an int, the return value must be an int?
Is this the correct understanding??
-JG
January 22, 2002 at 10:35 am
Yes, unless an implicit conversion can be made. Try this:
create table MyTest
( myid int
, MyVal char( 1)
)
go
insert MyTest (MyID) values (1)
insert MyTest (MyVal) values ('A')
go
select * from MyTest
select
MyID
, isnull( MyVal, ' ')
from MyTest
select
isnull( MyID, 0)
, isnull( MyVal, ' ')
from MyTest
select
isnull( cast( MyID as char), 'Z')
, isnull( MyVal, 0)
from MyTest
go
drop table MyTest
Steve Jones
January 23, 2002 at 11:43 am
So yvan should end up with something like this?
replace(isnull(cast(My_Column as char), 'NULL'), 'NULL', '')
January 23, 2002 at 11:56 am
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply