Replace the Null values

Question

Replace the Null values

HBK_4700

SSCrazy Eights

Points: 8932
More actions
September 16, 2010 at 7:04 pm

#145187

I need to replace the NULL values with empty space in a sql server 2005 table column data. Is there any way to replace data in table itself.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

I do not understand why you wish to do this to values in a table and not in the T-SQL statement which selects the data from the table, but you could look at this to understand the ISNULL function, which you could then use in a update statement.

[http://technet.microsoft.com/en-us/library/ms184325(SQL.90).aspx/url]

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 2

Actually I need to export the data to excel file. Some the coulumns data have NULL values.

I have used ISNULL function and wrote a below T-SQL statement, but getting 0 in place of NULL where as I am looking for a empty space

Just wondering whether SQL does't support empty space

or

is there any other ways to get it done.

Thank you.

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 3

laddu4700 (9/16/2010)
Actually I need to export the data to excel file. Some the coulumns data have NULL values.
I have used ISNULL function and wrote a below T-SQL statement, but not getting 0 in place of NULL where as I am looking for a empty space
Just wondering whether SQL does't support empty space
or
is there any other ways to get it done.
Thank you.

SQL does support empty space... just not for numerics. You'll need to convert your numerics to something character based so you can replace nulls with an empty string. Excel will take it all in stride during the import.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 4

Hi Jeff, Thanks for your reply.

I am getting empty space by using below query. Please correct me if I am wrong.

USE MYDB;

GO

select UserName, ISNULL(NULL, '') AS 'UserID'

FROM Tablename;

GO

Jeff Moden (9/16/2010)
laddu4700 (9/16/2010)
Actually I need to export the data to excel file. Some the coulumns data have NULL values.
I have used ISNULL function and wrote a below T-SQL statement, but not getting 0 in place of NULL where as I am looking for a empty space
Just wondering whether SQL does't support empty space
or
is there any other ways to get it done.
Thank you.
SQL does support empty space... just not for numerics. You'll need to convert your numerics to something character based so you can replace nulls with an empty string. Excel will take it all in stride during the import.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

laddu4700 (9/16/2010)
Hi Jeff, Thanks for your reply.
I am getting empty space by using below query. Please correct me if I am wrong.
USE MYDB;
GO
select UserName, ISNULL(NULL, '') AS 'UserID'
FROM Tablename;
GO
Jeff Moden (9/16/2010)
laddu4700 (9/16/2010)
Actually I need to export the data to excel file. Some the coulumns data have NULL values.
I have used ISNULL function and wrote a below T-SQL statement, but not getting 0 in place of NULL where as I am looking for a empty space
Just wondering whether SQL does't support empty space
or
is there any other ways to get it done.
Thank you.
SQL does support empty space... just not for numerics. You'll need to convert your numerics to something character based so you can replace nulls with an empty string. Excel will take it all in stride during the import.

Use this USE MYDB;

GO

select UserName, ISNULL(Userid, '') AS 'UserID'

FROM Tablename;

GO

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

See declare @t table ( id int )

insert into @t

select 1

union

select 2

union

select null

select *, isnull(id, '') as new from @t

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 7

I am getting empty space if I use below query

USE MYDB;

GO

select UserName, ISNULL(NULL, '') AS 'UserID'

FROM Tablename;

GO

I am getting 0 if I use below query

USE MYDB;

GO

select UserName, ISNULL(Userid, '') AS 'UserID'

FROM Tablename;

GO

--

But I need to replace NULL with empty space on where I have the NULL values.

In UserID column data all the rows are not NULL, only some of the rows. I need to replace them only with empty space.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

USE MYDB;

GO

select UserName, ISNULL(Cast(Userid AS VARCHAR(10)), '') AS 'UserID'

FROM Tablename;

GO

You can't set an int column to '', it'lll be implicitly converted to 0. Hence the int column needs to become character data first.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Janki Old Hand Points: 369 More actions · Answer 9

If You have say table "Employee" and column Emp_Name(that contains , NULL values)

Then.

Example:

Update Employee

SET Emp_Name = ISNULL(Emp_Name , ' ')

where <condition>.....................(If required, or all wherever there are NULL value in Emp_Name column,

those records will get replaced by blank value(Means nothing))

vliet SSCommitted Points: 1998 More actions · Answer 10

If you export values from SQL Server to Excel, NULL values should become empty cells in the spreadsheet without any conversion at all. However, I suspect that you want to copy a result from a query in SQL Server Management Studio into an Excel worksheet. If you define a VIEW for this query and import the data directly in Excel using that VIEW, then NULL values will indeed turn into empty cells in your worksheet, and you can rerun your query time after time directly from within Excel. If you want to distribute the results, it is very easy to remove only the query and leace the results in place.

smgilbert101 SSC Journeyman Points: 84 More actions · Answer 11

laddu4700 (9/16/2010)
Actually I need to export the data to excel file. Some the coulumns data have NULL values.
I have used ISNULL function and wrote a below T-SQL statement, but not getting 0 in place of NULL where as I am looking for a empty space
Just wondering whether SQL does't support empty space
or
is there any other ways to get it done.
Thank you.

I would suggest that if you are doing this because of the Excel requirement, you should reconsider where you need to replace the nulls. Use the IsNull statements mentioned in the SQL used to create your export file. Replacing the nulls in the table itself can create unintended consquences for your users. Nulls are not necessarilly a bad thing. This is especialy true if your users are doing analytics or aggregations down stream.