Replace NULL with a Blank space

_{Sunday, June 6, 2010 11:41 AM}

Hi All,

I have some data inserted into a temporary table, in which some fields have Null value. At the end of my procedure all data from temporary table is coped across the Main table. Now i want to replace those Null values inserted into #temp table with Blank space. 

I tried using ISNULL() function, but i do not seem to get it to work in my scenario. Can anyone help me here as to where im going wrong. Please find the associated code as shown below.

create table #Temp( Location NVARCHAR(100),
             Id INT,
             UniqueNum NVARCHAR(100),
             Total events INT,
             Date1 DATETIME,
             Date2 DATETIME,
             LongestTime NVARCHAR(100),
             AverageTime NVARCHAR(100),
            )

Something like below is used to insert data into the #Temp table.

INSERT INTO #Temp VALUES
(@Location, null, null, @EventCount, null, null, @LongestTime3, @AverageTime3)


Once data is inserted , at the very end it is then copied across to the main table.

Insert into dbo.tblmainTable
select Location,Id,UniqueNum, TotalEvents,Date1 ,Data2 ,LongestTime ,AverageTime
from #TempMobSer

Now im entering the below queries after the main Insert query to convert the Null values to Blank spaces.

select @Id = isnull(@Id,'') from dbo.tblMainTablee
select @UniqueNum = isnull(@UniquelNum,'') from dbo.tblMainTablee
select @Date1 = isnull(@Date1,'') from dbo.tblMainTablee
select @Date2= isnull(@Date2,'') from dbo.tblMainTablee


The above statement executes successfully, but the NULL values are not replaced by Blank spaces

Thanks.

Sagar

_{Sunday, June 6, 2010 2:06 PM ✅Answered}

Hi,

I was able to fix it, the MainTable still had old types, so that;s why it was not showing Blank spaces. Fixed it and now I am getting blank spaces for the 4 fields.

Didn't require to run the update query. I did the below.

SET the 2 fields as NULL and used ISNULL to convert the NULL to Blank space, and then inserted the fields into the #Temp tables.

Thanks for your help Uri.

Sagar

_{Sunday, June 6, 2010 12:00 PM}

I am using Sql Server 2000...Sagar

_{Sunday, June 6, 2010 12:10 PM}

>The above statement executes successfully, but the NULL values are not replaced by >Blank spaces
 Yes it will be converted to 0 (INT high precedence), see the below example

create

table #tmp ( id int)

insert

into #tmp values (1)

insert

into #tmp values (NULL)

select

isnull(id,'') from #tmp

BTW ,  you can issue one SELECT statement insted of few

select

@Id = isnull(@Id,''), @UniqueNum = isnull(@UniquelNum,''),

@Date1

= isnull(@Date1,'')  from dbo.tblMainTablee

And if you want to replace with soemthing else than NULL perhaps  you want to create a table with columns that have DEFAULT constrains  such as

create

table #tmp ( id int default 0,c char(1) default 'A')

insert

into #tmp (id) values(1)

select

* from #tmp

_{Sunday, June 6, 2010 12:49 PM}

Thanks for the reply Uri. My main requirement is to replace those 4 fields having NULL values when inserted into MainTable into Blank spaces. So once those NULL values are inserted, I need to update the MainTable so that those NULL values are replaced by Blank Spaces. How can this be achieved. Thanks in advance.Sagar

_{Sunday, June 6, 2010 1:25 PM}

Hi

So you understood that  you cannot update an INTEGER with '' , so others columns  you can use something like that 

UPDATE tbl SET col=''

WHERE col IS NULL

_{Sunday, June 6, 2010 1:51 PM}

Thanks Uri for the reply. It helped to some extent.

I tried using the Update Statement on Id, UniqueNum, Date1 and Date2 fields. Out of the four, only UniqueNum field displayed Blank space.

For Id im getting 0 ( I had changed the type in #Temp for thi to be a NVARCHAR, but in the table from where values are retrieved by #Temp Id is INT - is that the reason it is not showing Blank space ??)

For Date1 and Date2 fields im getting - 1900-01-01 00:00:00.000 (Date1 and Date2 fields are DATETIME fields in both #Temp and the table from where #Temp retreives the values , is that the reason??)

How can I fix the above issues?

Thanks.

Sagar