October 9, 2018 at 5:08 am
Hi,
I need to extract a part of the string which is the date. For. e.g. - to extract '310818' from the string 'RTBHY_BH_DK_Monthly_Physical_SalesDia_OrdRen_010818_310818.xlsx
the logic is to read the full length of the string and then go backwards to extract the 6 characters before '.xlsx'
I have tried doing this through FindString but its not working as desired.
Could somebody know how to achieve this in SSIS ? Thanks.
October 9, 2018 at 5:23 am
pwalter83 - Tuesday, October 9, 2018 5:08 AMHi,I need to extract a part of the string which is the date. For. e.g. - to extract '310818' from the string 'RTBHY_BH_DK_Monthly_Physical_SalesDia_OrdRen_010818_310818.xlsx
the logic is to read the full length of the string and then go backwards to extract the 6 characters before '.xlsx'
I have tried doing this through FindString but its not working as desired.
Could somebody know how to achieve this in SSIS ? Thanks.
Try this:replace(lower(TOKEN( @[User::TestString] , "_", TOKENCOUNT( @[User::TestString] , "_")) ),".xlsx","")
replacing @[User::TestString] with your variable/column name.
It returns the part of the string after the final underscore and then removes .xlsx from that.
It uses the LOWER() function to avoid potential issues with capitals, because REPLACE is case-sensitive in SSIS.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 9, 2018 at 5:35 am
Phil Parkin - Tuesday, October 9, 2018 5:23 AMpwalter83 - Tuesday, October 9, 2018 5:08 AMHi,I need to extract a part of the string which is the date. For. e.g. - to extract '310818' from the string 'RTBHY_BH_DK_Monthly_Physical_SalesDia_OrdRen_010818_310818.xlsx
the logic is to read the full length of the string and then go backwards to extract the 6 characters before '.xlsx'
I have tried doing this through FindString but its not working as desired.
Could somebody know how to achieve this in SSIS ? Thanks.Try this:
replace(lower(TOKEN( @[User::TestString] , "_", TOKENCOUNT( @[User::TestString] , "_")) ),".xlsx","")replacing @[User::TestString] with your variable/column name.
It returns the part of the string after the final underscore and then removes .xlsx from that.
It uses the LOWER() function to avoid potential issues with capitals, because REPLACE is case-sensitive in SSIS.
Hi Phil, Thanks a lot, your code worked perfectly !
Do you know if its possible to convert the extracted string - 310818 to a date format within the same logic to read as 2018-08-31 ?
thanks again.
October 9, 2018 at 5:46 am
pwalter83 - Tuesday, October 9, 2018 5:35 AMPhil Parkin - Tuesday, October 9, 2018 5:23 AMpwalter83 - Tuesday, October 9, 2018 5:08 AMHi,I need to extract a part of the string which is the date. For. e.g. - to extract '310818' from the string 'RTBHY_BH_DK_Monthly_Physical_SalesDia_OrdRen_010818_310818.xlsx
the logic is to read the full length of the string and then go backwards to extract the 6 characters before '.xlsx'
I have tried doing this through FindString but its not working as desired.
Could somebody know how to achieve this in SSIS ? Thanks.Try this:
replace(lower(TOKEN( @[User::TestString] , "_", TOKENCOUNT( @[User::TestString] , "_")) ),".xlsx","")replacing @[User::TestString] with your variable/column name.
It returns the part of the string after the final underscore and then removes .xlsx from that.
It uses the LOWER() function to avoid potential issues with capitals, because REPLACE is case-sensitive in SSIS.
Hi Phil, Thanks a lot, your code worked perfectly !
Do you know if its possible to convert the extracted string - 310818 to a date format within the same logic to read as 2018-08-31 ?
thanks again.
Of course it is, though it's not pretty.
In the following, if you replace @[User::DateString] with your entire expression from the previous step you should get to the answer you require in one hit.
"20"+right( @[User::DateString] ,2 ) + "-" + SUBSTRING( @[User::DateString] , 3,2 )+"-" + left( @[User::DateString],2)
If you are using variables to do this, there's no shame in using an intermediate variable to break apart the logic. Makes it easier to maintain.
--Edit: those smileys crack me up!
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 9, 2018 at 7:31 am
Phil Parkin - Tuesday, October 9, 2018 5:46 AMpwalter83 - Tuesday, October 9, 2018 5:35 AMPhil Parkin - Tuesday, October 9, 2018 5:23 AMpwalter83 - Tuesday, October 9, 2018 5:08 AMHi,I need to extract a part of the string which is the date. For. e.g. - to extract '310818' from the string 'RTBHY_BH_DK_Monthly_Physical_SalesDia_OrdRen_010818_310818.xlsx
the logic is to read the full length of the string and then go backwards to extract the 6 characters before '.xlsx'
I have tried doing this through FindString but its not working as desired.
Could somebody know how to achieve this in SSIS ? Thanks.Try this:
replace(lower(TOKEN( @[User::TestString] , "_", TOKENCOUNT( @[User::TestString] , "_")) ),".xlsx","")replacing @[User::TestString] with your variable/column name.
It returns the part of the string after the final underscore and then removes .xlsx from that.
It uses the LOWER() function to avoid potential issues with capitals, because REPLACE is case-sensitive in SSIS.
Hi Phil, Thanks a lot, your code worked perfectly !
Do you know if its possible to convert the extracted string - 310818 to a date format within the same logic to read as 2018-08-31 ?
thanks again.Of course it is, though it's not pretty.
In the following, if you replace @[User::DateString] with your entire expression from the previous step you should get to the answer you require in one hit.
"20"+right( @[User::DateString] ,2 ) + "-" + SUBSTRING( @[User::DateString] , 3,2 )+"-" + left( @[User::DateString],2)If you are using variables to do this, there's no shame in using an intermediate variable to break apart the logic. Makes it easier to maintain.
--Edit: those smileys crack me up!
Thanks a ton again !!! It worked perfectly.
Loved the smileys as well 🙂 !!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply