求一条拆分1个字段成多个字段的SQL语句（要高性能）
--原始数据
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'
--原始数据
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)
*/
