求一条拆分1个字段成多个字段的SQL语句（要高性能）

Question

--原始数据
DECLARE @TB TABLE(VALUE1 NVARCHAR(50))
INSERT INTO @TB
SELECT 'S12-2-3-34' UNION ALL
SELECT 'BS-61-2-33-4' UNION ALL
SELECT 'S-HG1-52-35-4-56' UNION ALL
SELECT 'SSS-1-2S-3-5-G-4' UNION ALL
SELECT 'S1234' UNION ALL
SELECT 'BS1234' --将VALUE1拆成4个字段A1,A2,A3,A4
--规则：最后一个横杆后的内容为A4，倒数第二个横杆后的为A3,倒数第三个横杆后的为A2，剩余为A1，没横杆则整个为A4
--要求：高性能，因为数据量有100W
--目标效果：
SELECT 'S12' AS A1,'2' AS A2,'3' AS A3,'34' AS A4 UNION ALL
SELECT 'BS-61','2','33','4' UNION ALL
SELECT 'S-HG1-52','35','4','56' UNION ALL
SELECT 'SSS-1-2S-3','5','G','4' UNION ALL
SELECT '','','','S1234' UNION ALL
SELECT '','','','BS1234'

Answer 1

论坛中1/10的帖子是问怎么组合字段，还有很多拆分字段的。说实话，不得不吐槽一下，SQL不是干这个的。

Answer 2

目前只想到用reverse + substring + charindex这种方法
不知道效率如何

Answer 3

--原始数据
DECLARE @TB TABLE(VALUE1 NVARCHAR(50))
INSERT INTO @TB
SELECT 'S12-2-3-34' UNION ALL
SELECT 'BS-61-2-33-4' UNION ALL
SELECT 'S-HG1-52-35-4-56' UNION ALL
SELECT 'SSS-1-2S-3-5-G-4' UNION ALL
SELECT 'S1234' UNION ALL
SELECT 'BS1234' ;with cte as
(
select t5.VALUE1,reverse(substring(reverse(t5.VALUE1),0,charindex('-',reverse(t5.VALUE1)))) A1,t5.A2,t5.A3,t5.A4
from
(
select t4.VALUE1,reverse(substring(reverse(t4.VALUE1),0,charindex('-',reverse(t4.VALUE1)))) A2,t4.A3,t4.A4
from
(
select replace(t3.VALUE1,'-'+t3.A3,'') VALUE1,t3.A3,t3.A4
from 
( 
select t2.VALUE1,reverse(substring(reverse(t2.VALUE1),0,charindex('-',reverse(t2.VALUE1)))) A3,t2.A4
from
(
select replace(t1.VALUE1,'-'+t1.A4,'') VALUE1,t1.A4
from 
( 
select VALUE1,reverse(substring(reverse(VALUE1),0,charindex('-',reverse(VALUE1)))) A4
from @TB
) t1
) t2
) t3
) t4
) t5
)
select A1,A2,A3,case when charindex('-',VALUE1)=0 then VALUE1 else A4 end A4 from cte/*
(6 row(s) affected)
A1                                                                                                                                                                                                                                                               A2                                                                                                                                                                                                                                                               A3                                                                                                                                                                                                                                                               A4
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2                                                                                                                                                                                                                                                                2                                                                                                                                                                                                                                                                3                                                                                                                                                                                                                                                                34
2                                                                                                                                                                                                                                                                2                                                                                                                                                                                                                                                                33                                                                                                                                                                                                                                                               4
35                                                                                                                                                                                                                                                               35                                                                                                                                                                                                                                                               4                                                                                                                                                                                                                                                                56
5                                                                                                                                                                                                                                                                5                                                                                                                                                                                                                                                                G                                                                                                                                                                                                                                                                4
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   S1234
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   BS1234
(6 row(s) affected)*/

Answer 4

目前也没有想到更好的方法，本来想写在一个select里的，但是这样很会长，所以分开写了。