Replace NULL with empty space.

Question

Post reply

Replace NULL with empty space.

HBK_4700

SSCrazy Eights

Points: 8932
More actions
September 16, 2010 at 8:56 pm

#141934

I am trying to export the SQL Server 2005 database tables(10) to csv files.
But some of the tables columns data have NULL values. I need to replace the NULL values with empty space. Easy way to find and replace in csv file.
I tried to write the T-sql statement using ISNULL function. But I could see 0 in NULL place.
Is there any way in SSIS where I can get it done? Appriciate your help.

Viewing 10 posts - 1 through 9 (of 9 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

what was the statement that you used for isnull?

What is the datatype that has null values in the database?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

laddu4700 (9/16/2010)
I am trying to export the SQL Server 2005 database tables(10) to csv files.
But some of the tables columns data have NULL values. I need to replace the NULL values with empty space. Easy way to find and replace in csv file.
I tried to write the T-sql statement using ISNULL function. But I could see 0 in NULL place.
Is there any way in SSIS where I can get it done? Appriciate your help.

You'll need to convert all numerics to strings if you want ISNULL(somenumber,'') to actually convert to an empty space.

--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 3

I used below query

USE dbname;

GO

select column1, column2, ISNULL(Column3, ' ') AS 'xxxx'

FROM Table1;

GO

column3 (FK, int, null)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Jeff's response should help you fix that. You can use the convert function in TSQL to change the int data type to a varchar or char (string) datatype so you can display an empty string rather than a 0.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 5

CirquedeSQLeil (9/16/2010)
Jeff's response should help you fix that. You can use the convert function in TSQL to change the int data type to a varchar or char (string) datatype so you can display an empty string rather than a 0.

And to be complete so the OP knows why this is happening: an empty string is converted to a 0 when you cast it to an int.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 6

I created below script and added to source in SSIS package, destination I have taken csv file.

when I execute the package I am getting below error.

Column 'OfficePhone' cannot convert between unicode and non-unicode string data types

SELECT [UserID]

,[UserName]

,ISNULL(Cast(OfficePhone AS VARCHAR(10)), '') AS 'OfficePhone'

,ISNULL(Cast(OfficeFax AS VARCHAR(10)), '') AS 'OfficeFax'

,CONVERT(varchar(10), DateCreated, 110) + SUBSTRING(CONVERT(varchar, DateCreated,

109), 12, 9) + SUBSTRING(CONVERT(varchar, DateCreated, 109),25,2)as DateCreated

,CONVERT(varchar(10), ModifiedDate, 110) + SUBSTRING(CONVERT(varchar, ModifiedDate,

109), 12, 9) + SUBSTRING(CONVERT(varchar, ModifiedDate, 109),25,2)as ModifiedDate

,[UserZipcode]

FROM [DBName].[dbo].[Table1]

Please advice,whether I need to add a transformation to get it done.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Yes, I would add a transformation task and include all of the columns in this case.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 8

CirquedeSQLeil (9/17/2010)
Yes, I would add a transformation task and include all of the columns in this case.

Thanks for your reply. Can you please explain in details, what transformation need to add and how to map.

Kind of new to SSIS packages. Appreciate your help.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Try this as a starting point

http://www.bimonkey.com/2009/06/the-data-conversion-transformation/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events