您能否幫助我如何提取 String1 、 String 2 和 String 3 以在我的 SQL 查詢中使用它們。
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;
uj5u.com熱心網友回復：
請嘗試以下解決方案。
它基于通過 XML/XQuery進行的標記化。
之后，很容易根據其位置拾取任何令牌。
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;
輸出
--------- --------- ---------
| Sub1 | Sub2 | Sub3 |
--------- --------- ---------
| String1 | String2 | String3 |
--------- --------- ---------
uj5u.com熱心網友回復：
這是一種替代方法，我用來string_split為每個值創建行，而不是過濾行并最終將行轉為列。
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
結果是
sub1 sub2 sub3
String1 String2 String3
這是DBFiddle
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/430086.html