Help with SubString in SSIS

Question

Hallo All,

I have a little Problem with the SubString-Function in SSIS (Derived Column).

So on..
That are the possible Input Strings:

Toys|Category|Cars|Lego

Toys|Hot&New|Girls&Lifestyle|Doll

Toys|New

Barbies|Category|Mobile

I need to filter the word after the Second " | ".

In first case: "Cars"

In the second case: "Girls&Lifestyle"

In the third case: ""

In the fourth case: "Mobile"

My try was: SUBSTRING(Category,FINDSTRING(Category,"|",2)+1,FINDSTRING(Category,"|",3))

It doesn't work because the FINDSTRING(Category,"|",3) gives the index but I need the lenght of the Word!! How can i count the Characters of a word in a String?

Thank you for your Help and Sorry for my bad english!!

Best Regards Alex

Supersonic

Answer 1

This should do what you want...a touch messy though!

SUBSTRING( Category, FINDSTRING( Category, "|", 2 )+1,FINDSTRING( Category, "|", 3 )-FINDSTRING( Category, "|", 2 )-1)

Kastner

Noticed that it doesn't quite work for strings with only 2 categories, here is an updated version : SUBSTRING( @[User::Category], FINDSTRING( @[User::Category], "|", 2 )+1,FINDSTRING( @[User::Category]+"|", "|", 3 )-FINDSTRING( @[User::Category], "|", 2 )-1) – Kastner