方法一:嵌套CHARINDEX调用。
使用CHARINDEX('_', stringvalue)
获取stringvalue
中第一个_
的位置。如果将该位置作为第三个参数传递给CHARINDEX
,可以指示它从特定位置开始搜索:
CHARINDEX(`_`, <i>stringvalue</i>, <i>startpos</i>)
现在,如果起始位置是CHARINDEX('_', stringvalue)+1
的结果,就像这样:
CHARINDEX(`_`, <i>stringvalue</i>, CHARINDEX(`_`, <i>stringvalue</i>) + 1)
这将给你第二个 _
的位置。因此,要找到第五个 _
,你需要再次嵌套使用 CHARINDEX
函数三次:
WITH aTable AS (
SELECT
*
FROM
(VALUES
('KLPI_2012_CBBE2_E_12704_2012-09-21_13_59_52'),
('IYT_2012_CBBI1_S_66_2012-09-21_15_28_53')
) AS v (aStringColumn)
),
positions AS (
SELECT
aStringColumn,
Underscore1 = CHARINDEX('_', aStringColumn),
Underscore5 = CHARINDEX('_',
aStringColumn,
CHARINDEX('_',
aStringColumn,
CHARINDEX('_',
aStringColumn,
CHARINDEX('_',
aStringColumn,
CHARINDEX('_',
aStringColumn
) + 1
) + 1
) + 1
) + 1
)
FROM
aTable
)
SELECT
aSubstring = SUBSTRING(aStringColumn,
Underscore1 + 1,
Underscore5 - Underscore1 - 1
)
FROM
positions
;
方法二. 字符分割 + 排序。
使用数字表将每个字符串拆分为单个字符,并同时获取它们的位置。
对字符串中的每个字符进行排序。
获取两个子集:
1)包含字符_
和排名为1;
2)包含字符_
和排名为5。
将这些子集彼此连接。
像方法一一样使用_
#1和_
#5的相应位置来获取子字符串。
WITH aTable AS (
SELECT
*
FROM
(VALUES
('KLPI_2012_CBBE2_E_12704_2012-09-21_13_59_52'),
('IYT_2012_CBBI1_S_66_2012-09-21_15_28_53')
) AS v (aStringColumn)
),
split AS (
SELECT
t.aStringColumn,
aChar = SUBSTRING(t.aStringColumn, n.Number, 1),
Position = n.Number
FROM
aTable t
INNER JOIN Numbers n
ON n.Number BETWEEN 1 AND LEN(t.aStringColumn)
),
ranked AS (
SELECT
*,
rnk = ROW_NUMBER() OVER (PARTITION BY aStringColumn, aChar ORDER BY Position)
FROM
split
WHERE
aChar = '_'
)
SELECT
aSubstring = SUBSTRING(first.aStringColumn,
first.Position + 1,
fifth.Position - first.Position - 1
)
FROM
ranked first
INNER JOIN ranked fifth
ON first.aStringColumn = fifth.aStringColumn
WHERE
first.rnk = 1
AND fifth.rnk = 5
;
注意:这两种方法都假定每个
aStringColumn
值至少有 5 个下划线字符。