Derived Column Transformations Question

Posted on 2011-10-06
Last Modified: 2013-11-10

I have an ActiveX script that looks something like the following:

if rtrim(mid(record, 129,12)) = "" then
     record = mid(record, 1,128) + "000000000000" + mid(record, 141)
end if

if rtrim(mid(record,47,8)) = "" then  
     record = mid(record, 1,46) + "00000000" + mid(record, 55)
end if

If rtrim(mid(record,85,8)) = "" then
      record = mid(record, 1,84) + "00000000" + mid(record, 93)
end if

If rtrim(mid(record,97,8)) = "" then
     record = mid(record, 1,96) + "00000000" + mid(record, 105)
end if

Is it possible to replace this type of logic with a derived column transformation task?

Question by:yechan
    LVL 59

    Expert Comment

    by:Kevin Cross
    In SQL, CASE WHEN/THEN is equivalent to IF/THEN; SUBSTRING() works similar to MID(); + handles string concatenation. You could do something like:

    SELECT SUBSTRING(@record, 1, 5)
         + COALESCE(NULLIF(SUBSTRING(@record, 6, 5), REPLICATE(' ', 5)), REPLICATE('0', 5))
           + SUBSTRING(@record, 11, 36)
         + COALESCE(NULLIF(SUBSTRING(@record, 47, 8), REPLICATE(' ', 8)), REPLICATE('0', 8))
         + SUBSTRING(@record, 55, 200)


    - NULLIF() can be used to return NULL if a value equals some value in the second parameter. Therefore, I used NULLIF(record, {8 spaces}), then wrapped in COALESCE() or ISNULL() you can replace that content with {8 0's}. REPLICATE() is a handy function to ensure I did not mistype the number of spaces or 0's. The final 200 is an arbitrary length to ensure we go all the way to the end of the string. 200 worked because my variable was less than 200. Most folks would use LEN(record) here. This works like MID(record, 105) which starts at 105 and goes to the end.

    So in your case, the formula might look like:

    SUBSTRING(record, 1, 46)
    + COALESCE(NULLIF(SUBSTRING(record, 47, 8), REPLICATE(' ', 8)), REPLICATE('0', 8))
    + SUBSTRING(record, 55, 30)
    + COALESCE(NULLIF(SUBSTRING(record, 85, 8), REPLICATE(' ', 8)), REPLICATE('0', 8))
    + SUBSTRING(record, 96, 1)
    + COALESCE(NULLIF(SUBSTRING(record, 97, 8), REPLICATE(' ', 8)), REPLICATE('0', 8))
    + SUBSTRING(record, 105, 24)
    + COALESCE(NULLIF(SUBSTRING(record, 129, 12), REPLICATE(' ', 12)), REPLICATE('0', 12))
    + SUBSTRING(record, 141, LEN(record))

    Anyway, that is just a thought. You can always just recreate as you have it.

    CASE WHEN RTRIM(SUBSTRING(record, 47, 8)) = '' THEN '00000000' ELSE SUBSTRING(record, 47, 8) END

    Hope that helps!
    LVL 21

    Expert Comment

    As alwayes Mark gives the right answer but i'll add an SSIS expression since you asked about the derived column...

    If you'll use it in a derived column, you'll need an expression such as:
    TRIM( SUBSTRING( @record,129,12 ) ) == "" ?  (SUBSTRING( @record,1,128) +  "000000000000"  + SUBSTRING( @record, 141,1)) : @record

    Open in new window

    that post will help you...
    LVL 1

    Author Comment

    thanks for the input.  With the derived column task, I can use the "record" column only once?  Seems like once I use it, I can not select it again.

    LVL 21

    Accepted Solution

    Nope, you can select it as much as you can in the same expression or in many other expressions, what matters is if the expression will replace the existing column or form a new column.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now