SQL获取第二个和第三个下划线之间的字符串

Question

我需要从表中的列中提取某个字符串，作为SSIS包的一部分。

该列的内容格式类似于"TST_AB1_ABC123456_TEST"。

我需要在不改变太多包的情况下获得第二个和第三个"_"之间的字符串，例如"ABC123456"，所以如果可能的话，我宁愿在1个SQL命令中完成。

我已经尝试了一些不同的方法，使用SUBSTRING、REVERSE和CHARDINDEX，但不知道如何只获得那个字符串。

Answer 1

使用基本字符串函数：

SELECT
SUBSTRING(col,
CHARINDEX('_', col, CHARINDEX('_', col) + 1) + 1,
CHARINDEX('_', col, CHARINDEX('_', col, CHARINDEX('_', col) + 1) + 1) -
CHARINDEX('_', col, CHARINDEX('_', col) + 1) - 1)
FROM yourTable;

在notes格式中，上面对SUBSTRING的调用表示：

SELECT
SUBSTRING(<your column>,
<starting at one past the second underscore>,
<for a length of the number of characters in between the 2nd and 3rd
underscore>)
FROM yourTable;

在其他数据库上，如Postgres和Oracle，有子字符串索引和regex函数可以更优雅地处理上述问题。实际上，SQL Server的最新版本有一个STRING_SPLIT函数，可以在这里使用，但它不维护生成部分的顺序。

Answer 2

如果列值总是有4个部分，那么可以使用这样的PARSENAME((函数。

DECLARE @MyString VARCHAR(100)
SET @MyString = 'TST_AB1_ABC123456_TEST';
SELECT PARSENAME(REPLACE(@MyString, '_', '.'), 2)

Answer 3

您也可以使用Cross Apply来完成此操作。我添加了一个where子句，以确保没有3个下划线的字符串不会导致错误

with your_table as (select 'TST_AB1_ABC123456_TEST' as txt1)
select txt1, txt2
from your_table t1
where txt1 like '%_%_%_%'
cross apply (select charindex( '_', txt1) as i1) t2 -- locate the 1st underscore
cross apply (select charindex( '_', txt1, (i1 + 1)) as i2 ) t3 -- then the 2nd
cross apply (select charindex( '_', txt1, (i2 + 1)) as i3 ) t4 -- then the 3rd
cross apply (select substring( txt1,(i2+1), (i3-i2-1)) as txt2) t5 -- between 2nd & 3rd

输出

+------------------------+-----------+
|          txt1          |   txt2    |
+------------------------+-----------+
| TST_AB1_ABC123456_TEST | ABC123456 |
+------------------------+-----------+

演示