Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Replace NULL with empty space. Expand / Collapse
Author
Message
Posted Thursday, September 16, 2010 8:56 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, February 27, 2014 10:41 AM
Points: 361, Visits: 1,781
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.
Post #987857
Posted Thursday, September 16, 2010 9:39 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 21,343, Visits: 15,019
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #987864
Posted Thursday, September 16, 2010 9:44 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 36,794, Visits: 31,253
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #987866
Posted Thursday, September 16, 2010 9:47 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, February 27, 2014 10:41 AM
Points: 361, Visits: 1,781

I used below query

USE dbname;
GO
select column1, column2, ISNULL(Column3, ' ') AS 'xxxx'
FROM Table1;
GO


column3 (FK, int, null)
Post #987868
Posted Thursday, September 16, 2010 11:04 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 21,343, Visits: 15,019
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #987889
Posted Friday, September 17, 2010 3:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:55 PM
Points: 13,357, Visits: 10,222
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #988005
Posted Friday, September 17, 2010 1:20 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, February 27, 2014 10:41 AM
Points: 361, Visits: 1,781

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.
Post #988524
Posted Friday, September 17, 2010 1:30 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 21,343, Visits: 15,019
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #988530
Posted Friday, September 17, 2010 1:42 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, February 27, 2014 10:41 AM
Points: 361, Visits: 1,781
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.
Post #988535
Posted Friday, September 17, 2010 3:18 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 21,343, Visits: 15,019
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #988588
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse