Post reply

Derived Column to handled mulitple conditions in CASE statement?

  • June 3, 2013 at 10:06 pm

    #1620768

    Vikash, could you kindly help me with this string here http://www.sqlservercentral.com/Forums/Topic1458631-364-2.aspx#bm1459386

    I can't help myself and no one else can either, so far. I think you will know answer.

  • November 4, 2014 at 5:14 am

    #1756501

    Hi

    I have something very similar but there are nulls involved, not sure how this will affect the script.

    The field want to use derived column is a field with say 12 characters.

    Field1 - data is for example

    123456abcdef

    123456

    null

    The case statement I want to use is

    CASE WHEN FIELD1 IS NULL

    THEN NULL

    WHEN SUBSTRING(FIELD1 , 7, 6) IS NULL

    THEN ' '

    ELSE RTRIM(SUBSTRING(FIELD1 , 7, 6))

    END as FIELD2

    Running this Case statement would give results

    'abcdef'

    ''

    null

    How would I add then as a derived column.

    Then example 2 is tougher

    CASE WHEN FIELD1 IS NULL THEN NULL

    WHEN SUBSTRING(REPLICATE('0', 6 - LEN(FIELD1)) + CAST(FIELD1 AS VARCHAR(6)), 7, 6) IS NULL

    THEN 0

    ELSE RTRIM(SUBSTRING(REPLICATE('0', 6 - LEN(FIELD1)) + CAST(FIELD1 AS VARCHAR(6)), 7, 6))

    END

    My third question is would it be faster running this as a sql statement to import rows, or would a derived column with a fastload on the destination be faster. We are in this case talking about a few hundred million rows.

Viewing 2 posts - 16 through 16 (of 16 total)

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