SSIS Derived column and regular expressions

I have a data flow in SSIS similar to:

ID      CCODE
1        99024
2        71237
3        A4583
4        L8743

I have the following business rules:

•All codes that start with a “99” need to be first or “2” if there is no “99” code on the account.
•Next would be any codes that start with a “17” “12” “6” “2” “11”.
•Next would be the X-ray codes that start with a “7”.
•Then any codes that START with a letter, usually “A” “J” “L” “G” “Q” (HCPCS codes).

In order to sort, I was going to add a derived column in my data flow that added a new column and placed an order number in that column based on what it found in the CCODE column.

For example, I should get something like this:

ID      CCODE    DERIVEDORDER
1        99024            1
2        71237            3
3        A4583           4
4        L8743            4

Here is my derived column expression:

SUBSTRING(TransactionID,1,2) == "99" ? 1 : SUBSTRING(TransactionID,1,2) == "17" || SUBSTRING(TransactionID,1,2) == "12" || SUBSTRING(TransactionID,1,2) == "6%" || SUBSTRING(TransactionID,1,2) == "2%" || SUBSTRING(TransactionID,1,2) == "11" ? 2 : SUBSTRING(TransactionID,1,2) == "7%" ? 3 : SUBSTRING(TransactionID,1,2) == "[A-Z^]" ? 4 : 5

Everything works perfectly until I get to the last part - determining if CCODE starts with a letter.  That part does not work at all.  Specifically, the SUBSTRING(TransactionID,1,2) == "[A-Z^]" ? 4 : 5 part.

Is there a way using expressions in the derived column transform to determine if a string starts with letters?

JamesNT
JamesNTAsked:
Who is Participating?
 
vdr1620Connect With a Mentor Commented:
the expression cannot evaluate the value to TRUE...You can try use Codepoint instead Like CODEPOINT(Column).. using that you can check if the starting value is Alphabet or Number..

Numbers from 0 to 9 have a codepoint of 48-57 ..So you can check that condition in the IF else expression
0
 
JamesNTAuthor Commented:
Working result:

SUBSTRING(TransactionID,1,2) == "99" ? 1 : SUBSTRING(TransactionID,1,2) == "17" || SUBSTRING(TransactionID,1,2) == "12" || SUBSTRING(TransactionID,1,2) == "6%" || SUBSTRING(TransactionID,1,2) == "2%" || SUBSTRING(TransactionID,1,2) == "11" ? 2 : SUBSTRING(TransactionID,1,2) == "7%" ? 3 : CODEPOINT(SUBSTRING(TransactionID,1,1)) < 48 || CODEPOINT(SUBSTRING(TransactionID,1,1)) > 57 ? 4 : CODEPOINT(SUBSTRING(TransactionID,LEN(TransactionID) - 1,1)) < 48 || CODEPOINT(SUBSTRING(TransactionID,LEN(TransactionID) - 1,1)) > 57 ? 5 : 6

Ugly, but functional.

For TransactionID:

All that start with 99 - Order = 1
All that start with 17, 12, 6, 2, 11 - Order = 2
All that start with 7 - Order = 3
All that start with a CODEPOINT <> 48 - 57 - Order = 4
All that end with a CODEPOINT <> 48 - 57 - Order = 5
All else - Order = 6

JamesNT
0
 
JamesNTAuthor Commented:
THANKS!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.