how to use substring function to get the values

Question

Hello All,

Could you please tell me how to get the value after 'between', before 'and'  i.e., BOKSAM KAND LAMOG  also after 'and' and before '('
want to get the bold values (between, and are case senstive)

for example MatterColumn   has value  "Collapsed Statue: Matured Life # 007812 between BOKSAM KAND LAMOG and Bill APPER SRIM (Goper)"

right now i am doing this once is working and another one is saying issue with length character in substring

rtrim (ltrim (SUBSTRING(MatterColumn,CHARINDEX('between',MatterColumn  COLLATE Latin1_General_CS_AI)+8, ( Charindex(' and ', MatterColumn  COLLATE Latin1_General_CS_AI,CHARINDEX('between',MatterColumn  COLLATE Latin1_General_CS_AI)+8) - CHARINDEX('between',MatterColumn  COLLATE Latin1_General_CS_AI)-8 )  ) )) Part1,

rtrim (ltrim (SUBSTRING(MatterColumn,CHARINDEX(' and ',MatterColumn  COLLATE Latin1_General_CS_AI)+5, ( Charindex(' (', MatterColumn  COLLATE Latin1_General_CS_AI,CHARINDEX(' and ',MatterColumn  COLLATE Latin1_General_CS_AI)+8) - CHARINDEX(' and ',MatterColumn  COLLATE Latin1_General_CS_AI)-5 )  ) )) Part2

please help me

thanks in advance

asitti

Answer 1

You should use a combination of the following functions (in a Derived Column):

FINDSTRING
SUBSTRING
LOWER (or UPPER)

This will give you the position of "between", "and" and  "("
FINDSTRING(LOWER(myColumn)," between ",1)            => 40
FINDSTRING(LOWER([myColumn])," and ",1)                 => 66
FINDSTRING([myColumn]," (",1)                                     => 86

Those can be used in a substring (where 9 is the length of " between "):
SUBSTRING([myColumn],
FINDSTRING(LOWER([myColumn])," between ",1) + 9,
FINDSTRING(LOWER([myColumn])," and ",1) - FINDSTRING(LOWER([myColumn])," between ",1) - 9)

Other options are TSQL query in the source or some scripting in a Script Component.

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter