How to extract a string between two underscore

Ali Ahad 131 Reputation points
2022-08-27T02:13:03.38+00:00

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.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,702 questions
0 comments No comments
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 26,586 Reputation points
    2022-08-28T00:44:40.537+00:00

    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

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Sreeju Nair 12,666 Reputation points
    2022-08-27T05:34:32.063+00:00

    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)  
    

    235375-image.png

    Hope this helps

    0 comments No comments

  2. Ali Ahad 131 Reputation points
    2022-08-29T02:21:10.473+00:00

    Hi @Yitzhak Khabinsky ,

    TOKEN works also.

    Thanks,
    Ali.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.