SQL : String extraction from Path-CodePudding

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) = '\\long\path\file_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;

CodePudding user response：

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) = '\\long\path\file_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 |
 --------- --------- ---------

CodePudding user response：

This is an alternative, I use string_split to create rows for every value, than filter the rows and finally pivot the rows into columns.

declare @SOURCE_FILE VARCHAR(255) = '\\long\path\file_name_something_String1_String2_String3_datetime.csv'

declare @sub1 varchar(100)
declare @sub2 varchar(100)
declare @sub3 varchar(100)

select @sub1 = String1, @sub2 = String2, @sub3 = String3
from   ( select t2.value,
                t2.value as ColumnName
         from   ( select t.value,
                         t.one,
                         row_number() over (partition by t.one order by t.one) AS number,
                         (select count(value) from string_split(@SOURCE_FILE, '_')) as total
                  from   ( select 1 as one,
                                  value
                           from   string_split(@SOURCE_FILE, '_')
                           where value is not null
                           and   value <> ''
                         ) t
                ) t2
         where t2.number > 3
        and   t2.number < t2.total
       ) t3
pivot
(
  max(value)
  for ColumnName in (String1, String2, String3)
) p

select @sub1 as sub1, @sub2 as sub2, @sub3 as sub3

the result is

sub1    sub2    sub3
String1 String2 String3

and here is the DBFiddle