SQL : String extraction from Path

Could you please help on how can I extract String1 , String 2 and String 3 to use these in my SQL queries ..

DECLARE @SOURCE_FILE VARCHAR(255) = '\longpathfile_name_something_String1_String2_String3_datetime.csv


DECLARE @SOURCE_FILE_NAME VARCHAR(255) = REVERSE(SUBSTRING(REVERSE(@SOURCE_FILE),0 ,CHARINDEX('', REVERSE(@SOURCE_FILE))));

DECLARE @SUB1 VARCHAR(255) = extracted String1;

DECLARE @SUB2 VARCHAR(8) = extracted String2;

DECLARE @SUB3 VARCHAR(4) = extracted String3;

Answer

Please try the following solution.

It is based on tokenization via XML/XQuery.

After that it is very easy to pick up any token based on its position.

SQL

DECLARE @SOURCE_FILE VARCHAR(255) = '\longpathfile_name_something_String1_String2_String3_datetime.csv'
    , @separator CHAR(1) = '_';

DECLARE @xml_path XML = TRY_CAST('<root><r><![CDATA[' + 
        REPLACE(@SOURCE_FILE, @separator, ']]></r><r><![CDATA[') + 
        ']]></r></root>' AS XML);

DECLARE @SUB1 VARCHAR(255) = @xml_path.value('(/root/r[last()-3]/text())[1]', 'VARCHAR(255)')
    , @SUB2 VARCHAR(8) = @xml_path.value('(/root/r[last()-2]/text())[1]', 'VARCHAR(8)')
    , @SUB3 VARCHAR(14) = @xml_path.value('(/root/r[last()-1]/text())[1]', 'VARCHAR(14)');

-- just to see
SELECT @SUB1 AS Sub1, @SUB2 AS Sub2, @SUB3 AS Sub3;

Output

+---------+---------+---------+
|  Sub1   |  Sub2   |  Sub3   |
+---------+---------+---------+
| String1 | String2 | String3 |
+---------+---------+---------+