SQL 按分隔符分割列拆分金额写法

1. 建表插入数据

select 30 as je,'张三/李四/' as ry into temptable
insert into temptable select 300,'a/b/c'

select * from temptable

2.用 with as 查询出 每个出现的位置和长度

WITH w AS 
(
SELECT
a.je, a.ry, CHARINDEX('/', a.ry) AS STA, CHARINDEX('/', a.ry) - 1 AS LENS
FROM temptable AS A where isnull(a.ry,'')!='' and a.ry!='/'
UNION ALL
SELECT je, ry, CHARINDEX('/', ry, STA + 1) AS STA, CHARINDEX('/', ry, STA + 1) - STA - 1 AS LENS
FROM w WHERE (STA <> 0)
)
select * from w

----分析： STA=0的为无效记录，结尾不是分割符号的，最后的记录出不来。

3.拆分计算

WITH w AS 
(
SELECT
a.je, a.ry, CHARINDEX('/', a.ry) AS STA, CHARINDEX('/', a.ry) - 1 AS LENS
FROM temptable AS A where isnull(a.ry,'')!='' and a.ry!='/'
UNION ALL
SELECT je, ry, CHARINDEX('/', ry, STA + 1) AS STA, CHARINDEX('/', ry, STA + 1) - STA - 1 AS LENS
FROM w WHERE (STA <> 0)
)
SELECT SUBSTRING(ry, STA - LENS, LENS) AS ry ,je/(len(ry)-len(replace(ry,'/',''))) as je
FROM w WHERE (STA <> 0)

结论：结尾必须增加分割符号，否则丢失数据并计算错误。