Creating derived columns from column of variable length components

Question

I'm hoping to get your guidance on this. I have a column, consisting of variable length sections that needs to be split into separate additional columns. The column, PCMRetrievalCode is nvarchar type and consists of this format:

i.e. PO607CON1324

PO = Non-Package or PA = Package
607 = SchemaId --> could be more than 3 numbers, as the schema increases
CON = Container
1324 = ContainerId --> could be 3-5 numbers

So, in my SSIS package I'm trying to create these as derived columns, but am having trouble with the expression to split PCMRetrievalCode column, based on its variable length components for SchemaID and ContainerID. I've tried using the FINDSTRING function, since there's no CHARINDEX and am trying to use DT_WSTR, as this is the SSIS compatible datatype for the SQL Server nvarch datatype. These are the expressions I'm trying in the Transformation Editor:

I think the expressions for the PackageCode and SchemaID will hopefully be okay, but it's the ContainerID expression where I'm having difficulty. Any advice would be greatly appreciated. Thanks in advance!

KeithL · Answer 1 · 2018-04-24T16:51:17.830

2

I did it with 2 separate derived columns objects.

First one (GetPO and leftover for easier parsing):

POorPA = LEFT(data,2)
leftover = SUBSTRING(data,3,9999)

Next derived column is more complex:

ScemaID = LEFT(leftover,FINDSTRING(leftover,"C",1) - 1)
CON = SUBSTRING(leftover,FINDSTRING(leftover,"C",1),3)
ContainerID = RIGHT(leftover,FINDSTRING(REVERSE(leftover),"N",1) - 1)

Rationale and assumed concepts:

PO or PA is always 2 characters
CON is always present to target
The IDs are numeric (it is safe to search for "C" and "N")

edited Apr 24 '18 at 16:51

answered Apr 24 '18 at 16:41

KeithL

5,348
3
19
25

FYI i used [data] as the field name – KeithL Apr 24 '18 at 16:57

score 1 · Answer 2 · answered Apr 24 '18 at 16:20

I have ignored the data conversions for the time being to simplify the expression logic. If you need them, you should be able to add them in where necessary.

As you have a consistent CON value in your data you can use that as an anchor as you have done for the SchemaId and work from there. I think the part you are missing is that you don't have to give the exact amount of characters left in the string to the SUBSTRING expression. If you just want to get to the end, stick 99999 in there and be done with it:

SUBSTRING(PCMRetrievalCode
         ,FINDSTRING(PCMRetrievalCode
                    ,"CON"
                    ,1
                    ) + 3
         ,99999
         )

score 0 · Accepted Answer · answered May 10 '18 at 20:03

Thanks for your suggestions, All!

I was able to get it to work by using the following expressions for the derived columns:

PackageCode: SUBSTRING(((DT_WSTR,15)PCMRetrievalCode),1,2)

SchemaID:  SUBSTRING((DT_WSTR,15)PCMRetrievalCode,3,FINDSTRING(((DT_WSTR,15)  
           PCMRetrievalCode),"C",1) - 3)  

ContainerID:  RIGHT(((DT_WSTR,15)PCMRetrievalCode),(LEN((DT_WSTR,15)
              PCMRetrievalCode) - FINDSTRING((DT_WSTR,15)
              PCMRetrievalCode,"N",1)))

Creating derived columns from column of variable length components

3 Answers3