sql - 将列中的值分隔到 SSMS 中的不同段或列
问题描述
我有一张如下表TABLE1
:
YEAR | GL_CODE
-------------------------------------------
2019 | 141.0000.1001.732155.0000.000.0000.
2019 | 141.0000.0000.143402.0000.131.0000.
2019 | 541.000.00.00.00.149104.0000.000.00
2019 | 541.101.04.00.00.731104.0000.000.00
2019 | 141.0000.0000.151310.0000.000.0000.
2019 | 541.102.06.00.16.714101.7098.000.00
2019 | 111.00000.0511.766701.0000.000.0000
2019 | 111.00000.0512.520111.5003.331.0000
我需要将它分成不同的段或列,如下所示:
YEAR |SEGMENT1|SEGMENT2|SEGMENT3|SEGMENT4|SEGMENT5|SEGMENT6|SEGMENT7 |SEGMENT8|SEGMENT9
------------------------------------------------------------------------------------------
2019 | 141 | 0000 | 1001 |732155 | 0000 | 000 | 0000 | NULL | NULL
2019 | 141 | 0000 | 0000 |143402 | 0000 | 131 | 0000 | NULL | NULL
2019 | 541 | 000 | 00 | 00 | 00 |149104 | 0000 | 000 | 00
2019 | 541 | 101 | 04 | 00 | 00 |731104 | 0000 | 000 | 00
2019 | 141 | 0000 | 0000 |151310 | 0000 | 000 | 0000 | NULL | NULL
2019 | 541 | 102 | 06 | 00 |714101 |714101 | 7098 | 000 | 00
2019 | 111 | 00000 | 0511 |766701 | 0000 | 000 | 0000 | NULL | NULL
2019 | 111 | 00000 | 0512 |520111 | 5003 | 331 | 0000 | NULL | NULL
请注意,中的所有条目都没有固定数量的段。
我尝试使用:
select YEAR,PERIOD,SUBSTRING(GL_CODE,1,3) as segment1,
SUBSTRING(GL_CODE,
charindex('.', GL_CODE, 1)+1,
((charindex('.',GL_CODE, (charindex('.', GL_CODE, 1)+1))+1)-(charindex('.', GL_CODE, 1)+1)-1)
) as segment2,
SUBSTRING(GL_CODE,
charindex('.',GL_CODE, (charindex('.', GL_CODE, 1)+1))+1,
(charindex('.',GL_CODE, (charindex('.', GL_CODE, (charindex('.', GL_CODE, 1)+1))+1))+1)-
(charindex('.',GL_CODE, ( charindex('.', GL_CODE, 1)+1))+1)-1
) as segment3,
SUBSTRING(GL_CODE,
charindex('.',GL_CODE, (charindex('.', GL_CODE, (charindex('.', GL_CODE, 1)+1))+1))+1,
(charindex('.',GL_CODE,(charindex('.', GL_CODE, (charindex('.', GL_CODE, (charindex('.', GL_CODE, 1)+1))+1))+1))+1 )-(charindex('.',GL_CODE, (charindex('.', GL_CODE, (charindex('.', GL_CODE, 1)+1))+1))+1)-1
) as segment4,
SUBSTRING(GL_CODE,
charindex('.',GL_CODE,(charindex('.', GL_CODE, (charindex('.', GL_CODE, (charindex('.', GL_CODE, 1)+1))+1))+1))+1,(charindex('.',GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE, (charindex('.', GL_CODE, (charindex('.',GL_CODE, 1)+1))+1))+1))+1))+1)-(charindex('.',GL_CODE,(charindex('.', GL_CODE, (charindex('.', GL_CODE, (charindex('.', GL_CODE, 1)+1))+1))+1))+1)-1
) as segment5,
SUBSTRING(GL_CODE,
charindex('.',GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE, (charindex('.', GL_CODE,
(charindex('.',GL_CODE, 1)+1))+1))+1))+1))+1,
(charindex('.',GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE,
(charindex('.', GL_CODE, (charindex('.', GL_CODE, 1)+1))+1))+1))+1))+1))+1)
-(charindex('.',GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE, (charindex('.', GL_CODE, (charindex('.',GL_CODE, 1)+1))+1))+1))+1))+1)-1
) as segment6,
SUBSTRING(GL_CODE,
charindex('.',GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE,
(charindex('.', GL_CODE, (charindex('.', GL_CODE, 1)+1))+1))+1))+1))+1))+1,
(charindex('.',GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE,
(charindex('.', GL_CODE, (charindex('.', GL_CODE, (charindex('.', GL_CODE, 1)+1))+1))+1))+1))+1))+1))+1)
-(charindex('.',GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE, (charindex('.', GL_CODE, (charindex('.', GL_CODE, 1)+1))+1))+1))+1))+1))+1)-1
) as segment7,
SUBSTRING(GL_CODE,
charindex('.',GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE,
(charindex('.', GL_CODE, (charindex('.', GL_CODE, (charindex('.', GL_CODE, 1)+1))+1))+1))+1))+1))+1))+1,
(charindex('.',GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE,
(charindex('.', GL_CODE, (charindex('.', GL_CODE, (charindex('.', GL_CODE,
(charindex('.', GL_CODE, 1)+1))+1))+1))+1))+1))+1))+1))+1)
-(charindex('.',GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE,(charindex('.', GL_CODE,
(charindex('.', GL_CODE, (charindex('.', GL_CODE, (charindex('.', GL_CODE, 1)+1))+1))+1))+1))+1))+1))+1)-1
) as segment8
from TABLE1;
我正在获取第 6 段的值...在第 6 段之后我遇到了错误。任何人都可以建议更改代码或替代方法吗?
提前致谢
阿克谢
解决方案
创建 UDF(用户定义函数)
CREATE FUNCTION dbo.fn_Split
(
@str varchar(max),
@delim char(1),
@columnCnt int = 50
)
RETURNS TABLE
AS
RETURN
(
SELECT *
FROM (SELECT
nn = (nn - 1) / @columnCnt + 1,
nnn = 'value' + cast(((nn - 1) % @columnCnt) + 1 as varchar(10)),
value
FROM (SELECT
nn = ROW_NUMBER() over (order by (select null)),
value
FROM string_split(@str, @delim) aa
) aa
where nn > 0
) bb
PIVOT
(
max(value)
FOR nnn IN (
value1, value2, value3, value4, value5, value6, value7, value8, value9
)
) AS PivotTable
)
然后在查询中使用该函数
CREATE TABLE #TBL ([YEAR] INT, GL_CODE VARCHAR(300))
INSERT INTO #TBL VALUES
(2019,'141.0000.1001.732155.0000.000.0000.'),
(2019,'141.0000.0000.143402.0000.131.0000.'),
(2019,'541.000.00.00.00.149104.0000.000.00'),
(2019,'541.101.04.00.00.731104.0000.000.00'),
(2019,'141.0000.0000.151310.0000.000.0000.'),
(2019,'541.102.06.00.16.714101.7098.000.00'),
(2019,'111.00000.0511.766701.0000.000.0000'),
(2019,'111.00000.0512.520111.5003.331.0000');
SELECT
[YEAR],
f.*
FROM #TBL
cross apply dbo.fn_Split(#TBL.GL_CODE, '.', DEFAULT) as f
输出
您可以将列名更改为您的
推荐阅读
- react-native - 我正在尝试将当前日期和时间存储在异步存储中,但我得到的返回值是一个承诺,我看不到这些值
- c++ - std::shared_ptr 的 use_count() 周围的完整内存屏障是否会使其成为可靠的计数器?
- powerbuilder - 在 Powerbuilder 12.5 中配置 Git
- javascript - 如何将异步 javascript 添加到 WordPress 菜单?
- emacs - orgmode 中的注释行
- c# - 如何使用模型任何类型的 sql c# 加入 IQueryable
- c# - 如何让它让你的玩家可以每隔 n 秒跳跃一次
- python - 在没有链式索引的情况下使用 ILOC
- javascript - 将 fetch 包装在 promise 中有什么好处?
- azure-cosmosdb - 在 Azure Cosmos DB 中使用 Gremlin 重命名属性