How to extract a string between two underscore

Question

How to extract a string between two underscore

Ali Ahad 151

I have a file name and I need to extract a certain string from the name:

CMDB_Transactions File January_yyyymmdd.xlsx and I want my output to be 'Transactions File January'. My current expression is as follows:

SUBSTRING(@USER ::FileName, ((FINDSTRING(@USER ::FileName, "_", 1)+1), the third argument for the SUBSTRING function (length) is where I am having issues.

Any help is appreciated.

Answer accepted by question author

2 additional answers

Your answer

Answer 1

Yitzhak Khabinsky 27,091

Hi @Ali Ahad ,

SSIS Expression function TOKEN() is very handy for such scenario.

Let's say User::fileName variable has value "CMDB_Transactions File January_yyyymmdd.xlsx"

TOKEN(@[User::fileName], "_", 2)

For the reference: token-ssis-expression

Answer 2

Sreeju Nair 12,756

See the following

set @Filename = 'CMDB_Transactions File January_yyyymmdd.xlsx'  
select SUBSTRING(@FileName, CHARINDEX('_', @Filename)+1, CHARINDEX('_', @Filename, CHARINDEX('_', @Filename)+1)-CHARINDEX('_', @Filename)-1)

Hope this helps

Answer 3

Ali Ahad 151

Hi @Yitzhak Khabinsky ,

TOKEN works also.

Thanks,
Ali.

Yitzhak Khabinsky 27,091 Reputation points

2022-08-29T02:56:21.543+00:00

@Ali Ahad ,

Your question is about SSIS Expression for the @USER ::FileName SSIS variable.
Somehow you accepted as an answer a T-SQL based solution.
T-SQL is not applicable in the SSIS Expressions.

Beyond that, you prefer a convoluted solution based on 5 function calls, i.e. SUBSTRING() and CHARINDEX(), instead one simple TOKEN() call. The TOKEN() function is made for scenarios like yours.

Share via

How to extract a string between two underscore

2 additional answers

Your answer