August 13, 2003 at 3:58 am
Is it possible to select everything from a table, replacing any NULL values with something else?
Something like:
SELECT ISNULL(*, '-1') FROM tablename
Thanks,
Ryan.
August 13, 2003 at 4:02 am
Try using the COALESCE function.
Relationships are like Banks.You Invest the Principal and then get the Interest.
He who knows others is learned but the wise one is one who knows himself.
August 13, 2003 at 4:15 am
No - you will have to specify specific columns for IsNull.
Jeremy
August 14, 2003 at 7:51 pm
Quote: No - you will have to specify specific columns for IsNull.
Nah, as long as you make it dynamic. Just set the variable @Table to your table name and this should work for you.
if object_ID('TMP') is not null drop table TMP
Create Table TMP (col1 int, col2 int, col3 int)
Insert TMP Values (1, NULL, 1)
Insert TMP Values (Null, 2, 2)
Insert TMP Values (3, 3, NULL)
/*-----------------------------------------------------------------
Looks through column names and builds csv with isnull(ColumnName, 0) function
*/-----------------------------------------------------------------
declare @Name varchar(255),
@Query varchar(8000),
@Table varchar(255)
select @Table = 'TMP',
@Name = ''
While 1=1
begin
--Use to lookup all columns in Table
select @Query = isnull(@Query + ', ', '') + 'isnull(' + sc.name + ', 0)',
@Name = sc.name
from sysobjects so (nolock)
JOINsyscolumns sc (nolock) on so.id = sc.id
Where so.name = @Table and sc.name > @Name
order by sc.name
if @@RowCount = 0 break
end
exec ('Select ' + @Query + ' From ' + @Table + ' (nolock)')
Signature is NULL
August 14, 2003 at 9:22 pm
The other thing to do after this would be to set defaults on those columns you dont want nulls in? This would minimize having to do this sorta thing in the first place.
------------------------------
Life is far too important to be taken seriously
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply