Replace NULL with empty space.

  • 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.

  • 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

  • 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)

  • I used below query

    USE dbname;

    GO

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

    FROM Table1;

    GO

    column3 (FK, int, null)

  • 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

  • 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

  • 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.

  • 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

  • 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.

  • 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

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

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