Post reply

Replace Blanks in the Derived Column Transform

  • February 23, 2010 at 9:59 am

    #138299

    Hi,

    I'm having some problems with the following. I have a flat file that has a column of data in that can either be an integer or a blank a "".

    I'm trying to use the Derived Column transform to replace the blank values with a zero.

    I tried the following expression -

    REPLACE(Column_1, "", "0")

    which didn't work. Is there something wrong with the syntax or is the "" the incorrect way to represent a blank?

    Thanks,

    Stephen

  • February 23, 2010 at 11:39 am

    #1123789

    Rather than your 'blank' "" - which is an empty string - your values may be coming in as NULL.

    Try this (untested) instead:

    IsNull([Column_1])? "0" : [Column_1]

    You can always add in your REPLACE function around this - then you'll fix both NULLs and empty strings.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • February 24, 2010 at 2:56 am

    #1124173

    Hi Phil,

    Thanks for the reply. I did try ISNULL but it still didn't catch it.

    What did work was Col1 == "" ? "0" : Col1.

    Strange why the replace didn't detect "" but the above did.

    Thanks any way,

    Stephen

  • February 24, 2010 at 3:02 am

    #1124176

    Strange indeed - thanks for posting back & well done.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

Viewing 4 posts - 1 through 3 (of 3 total)

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