使用SQL Server通过定界符在列中拆分字符串 [英] Split string in columns by delimiter using SQL Server
问题描述
我需要使用定界符在列中拆分我的一个列值,下面是表结构。
I need to split my one column value in column using delimiter, below is the table structure.
create table #a
(
id int,
timeline varchar(100)
)
insert into #a
values (1, 'Semi Annual Q2 (May/June/July) & Q4 (Nov/Dec/Jan)'),
(2, 'Semi Annual Q1 (Feb/Mar/Apr) & Q3 (Aug/Sep/Oct)'),
(3, 'Annual Q3 (Aug/Sep/Oct)'),
(4, 'Annual Q2 (May/June/July)'),
(5, 'Annual Q4 (Nov/Dec/Jan)'),
(6, 'Semi Annual Q1 (Jan/Feb/Mar) & Q3 (July/Aug/Sep)')
select * from #a
输出我想用'/'分隔符分割时间轴值,并为独立的月份和所有月份应按顺序排列,看起来像下面的示例。
Output I want to split timeline values by ' / ' delimiter and make separate column for separate month and all month should be in sequence, which look like a below sample.
ID M1 M2 M3 M4 M5 M6
---------------------------------------
1 May June July Nov Dec Jan
2 Feb Mar Apr Aug Sep Oct
3 Aug Sep Oct NULL NULL NULL
4 May June July NULL NULL NULL
5 Nov Dec Jan NULL NULL NULL
6 Jan Feb Mar July Aug Sep
到目前为止,我已经尝试过:
So far, I have tried this:
select
timeline,
substring((substring(timeline, CHARINDEX('(', timeline) + 1, len(timeline) - 1)), 1, charindex('/', substring(timeline, CHARINDEX('(', timeline) + 1, len(timeline) - 1)) - 1) as M1,
replace(replace(right(substring(substring(timeline, CHARINDEX('(', timeline) + 1, len(timeline)), 1, charindex(')', substring(timeline, CHARINDEX('(', timeline) + 1, len(timeline)))), charindex('/', reverse(substring(substring(timeline, CHARINDEX('(', timeline) + 1, len(timeline)), 1, charindex(')', substring(timeline, CHARINDEX('(', timeline) + 1, len(timeline))))), 4)), '/', ''), ')', '') as M3
from
#a;
这不是代码,也很繁琐。如果您有有效的方法,请提供帮助。
which is not a code and too tedious also. please help if you have efficient way to do this.
推荐答案
在以下解决方案中使用的SplitCSVToTable8K函数与DelimitedSplit8K相同。上面提到的函数...
The SplitCSVToTable8K function being used in the following solution, is the same DelimitedSplit8K function mentioned above...
在解决方案中如何使用它:
Here's how to use it in the solution:
WITH
cte_ParseTimeline AS (
SELECT
a.id,
rn = ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY sc.ItemNumber),
sc.Item
FROM
#a a
CROSS APPLY dbo.SplitCSVToTable8K(REPLACE(REPLACE(a.timeline, '(', '/'), ')', '/'), '/') sc
WHERE
sc.Item LIKE ('[A-Z][a-z][a-z]')
OR
sc.Item LIKE ('[A-Z][a-z][a-z][a-z]')
)
SELECT
pt.id,
M1 = MAX(CASE WHEN pt.rn = 1 THEN pt.Item END),
M2 = MAX(CASE WHEN pt.rn = 2 THEN pt.Item END),
M3 = MAX(CASE WHEN pt.rn = 3 THEN pt.Item END),
M4 = MAX(CASE WHEN pt.rn = 4 THEN pt.Item END),
M5 = MAX(CASE WHEN pt.rn = 5 THEN pt.Item END),
M6 = MAX(CASE WHEN pt.rn = 6 THEN pt.Item END)
FROM
cte_ParseTimeline pt
GROUP BY
pt.id;
结果...
id M1 M2 M3 M4 M5 M6
----------- ----- ----- ----- ----- ----- -----
1 May June July Nov Dec Jan
2 Feb Mar Apr Aug Sep Oct
3 Aug Sep Oct NULL NULL NULL
4 May June July NULL NULL NULL
5 Nov Dec Jan NULL NULL NULL
6 Jan Feb Mar July Aug Sep
这篇关于使用SQL Server通过定界符在列中拆分字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!