Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

Derived Column Transformations Question

Hi,

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?

thanks.
0
yechan
Asked:
yechan
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
In SQL, CASE WHEN/THEN is equivalent to IF/THEN; SUBSTRING() works similar to MID(); + handles string concatenation. You could do something like:

DECLARE @record VARCHAR(200) = 'ABCDE     FGHIJKLMNOPQRSTUVWXYZ0123456789ABCDE        FGHIJKLMNOPQRSTUVWXYZ0123456789'
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!
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
Hi,
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...
http://www.sqlchick.com/entries/2011/6/8/nested-conditional-operators-in-an-ssis-derived-column.html
0
 
yechanAuthor Commented:
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.

thanks
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
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.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now