--原始数据

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                                                                                                                                                                                                                     &nbs