Extract character from one column and populate them in 2 different columns ssis derived column

Question

I have a column ProductDetails which has values such as

j Jameson Mbc 6.5, abc 8 abc blah
Napa california Mbc 1 abc 3.5
Washington k Mbc 2.5 abc 6
New york city is awesome

i have thousands of records

I want to populate 2 other columns Mbc and abc as below

Mbc
6.5
1
2.5

abc
8
3.5
6

I tried (DT_STR,10,1252)substring(ProductDetails, findstring(“Mbc”, ProductDetails,1)+4,3)

For abc= (DT_STR,10,1252)substring(ProductDetails, findstring(“abc”, ProductDetails,1)+4,3)

I get result and some values are coming as 1C or 2b etc , just want numbers no characters

For column value such as New york city is awesome we just want to populate it with Null etc

Thanks

Answer 1

"abc" column

FINDSTRING( [ProductDetails],"abc", 1 ) > 0 ? SUBSTRING(SUBSTRING([ProductDetails], FINDSTRING( [ProductDetails],"abc", 1 ) + 4 , 3),1,LEN( SUBSTRING([ProductDetails], FINDSTRING( [ProductDetails],"abc", 1 ) + 4 , 3) ) - FINDSTRING( SUBSTRING([ProductDetails], FINDSTRING( [ProductDetails],"abc", 1 ) + 4 , 3) ," ",1)) : NULL(DT_WSTR,50)

"Mbc" column

FINDSTRING( [ProductDetails],"Mbc", 1 ) > 0 ? SUBSTRING(SUBSTRING([ProductDetails], FINDSTRING( [ProductDetails],"Mbc", 1 ) + 4 , 3),1,LEN( SUBSTRING([ProductDetails], FINDSTRING( [ProductDetails],"Mbc", 1 ) + 4 , 3) ) - FINDSTRING( SUBSTRING([ProductDetails], FINDSTRING( [ProductDetails],"Mbc", 1 ) + 4 , 3) ," ",1)) : NULL(DT_WSTR,50)