如何把三个以若干|和~分隔的字符串，输出成一张一定格式的表
举例：
@string1 = '56343~0~0.0~0.0~0.0|2.24~25628.28|2.26~193660.37|2.28~569704.62<更多诸如|2.24~25628.28不例举>'
@string2 = '25422~0~0.0~0.0~0.0|2.82~590.05|2.84~330.38|2.88~153.32<更多诸如|2.82~590.05不例举>'
@string3 = '58805~0~0.0~0.0~0.0|3.35~638.04|3.4~893.91|3.45~27770.75<更多诸如|3.35~638.04不例举>'
输出table
SelectionId PriceTag PriceValue
56343 2.24 25628.28
56343 2.26 193660.37
56343 2.28 569704.62
... ... ...
... ... ...
25422 2.82 590.05
25422 2.84 330.38
25422 2.88 153.32
... ... ...
... ... ...
58805 3.35 638.04
58805 3.40 893.91
58805 3.45 27770.75
... ... ...
... ... ...
不胜感谢！
[解决办法]
字符串拆分函数 精华帖子里面很多
[解决办法]
--笨办法
DECLARE @string1 VARCHAR(2000)
SELECT @string1 = '56343~0~0.0~0.0~0.0
[解决办法]
2.24~25628.28
[解决办法]
2.26~193660.37
[解决办法]
2.28~569704.62<更多诸如
[解决办法]
2.24~25628.28不例举>'
DECLARE @str VARCHAR(50),@str1 VARCHAR(50),@str2 VARCHAR(50),@str3 VARCHAR(50)
SELECT @str = LEFT(@string1,CHARINDEX('
[解决办法]
',@string1)-1)
SELECT @string1 = STUFF(@string1,1,CHARINDEX('
[解决办法]
',@string1),'')
SELECT @str1 = LEFT(@string1,CHARINDEX('
[解决办法]
',@string1)-1)
SELECT @string1 = STUFF(@string1,1,CHARINDEX('
[解决办法]
',@string1),'')
SELECT @str2 = LEFT(@string1,CHARINDEX('
[解决办法]
',@string1)-1)
SELECT @string1 = STUFF(@string1,1,CHARINDEX('
[解决办法]
',@string1),'')
SELECT @str3 = LEFT(@string1,CHARINDEX('
[解决办法]
',@string1)-1)
SELECT LEFT(@str,CHARINDEX('~',@str)-1) AS SelectionId,
LEFT(@str1,CHARINDEX('~',@str1)-1) AS PriceTag,
STUFF(@str1,1,CHARINDEX('~',@str1),'') AS PriceValue
UNION ALL
SELECT LEFT(@str,CHARINDEX('~',@str)-1),
LEFT(@str2,CHARINDEX('~',@str2)-1),
STUFF(@str2,1,CHARINDEX('~',@str2),'')
UNION ALL
SELECT LEFT(@str,CHARINDEX('~',@str)-1),
LEFT(@str3,CHARINDEX('~',@str3)-1),
LEFT(STUFF(@str3,1,CHARINDEX('~',@str3),''),CHARINDEX('<',STUFF(@str3,1,CHARINDEX('~',@str3),''))-1)
SelectionId PriceTag PriceValue
-------------------------------------------------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------------------
56343 2.24 25628.28
56343 2.26 193660.37
56343 2.28 569704.62
(3 行受影响)
if object_id('fn_MutiSplitTSQL')is not null drop function fn_MutiSplitTSQL
GO
create FUNCTION dbo.fn_MutiSplitTSQL
(@s NVARCHAR(MAX), @split NCHAR(1) ,@Sub@split NCHAR(1)= N',') RETURNS TABLE
AS
RETURN
select * from (SELECT d.rn ,'col'+ cast( n - LEN(REPLACE(LEFT(col, n), @Sub@split, '')) + 1 as varchar(10)) AS attribute,
SUBSTRING(col, n, CHARINDEX(@Sub@split, col + @Sub@split, n) - n) AS value
FROM ( SELECT n - LEN(REPLACE(LEFT(array, n), @split, '')) + 1 AS rn,
SUBSTRING(array, n, CHARINDEX(@split, array + @split, n) - n) AS col
FROM (SELECT @s AS array) AS D
JOIN dbo.Nums a
ON n <= LEN(array)
AND SUBSTRING(@split + array, n, 1) = @split ) AS D
JOIN dbo.Nums a
ON n <= LEN(col)
AND SUBSTRING(@Sub@split + col, n, 1) = @Sub@split ) as d
PIVOT(max(value) FOR attribute
IN(col1,col2 ,col3,col4,col5 )) AS P
GO
declare @string1 varchar(8000) = '56343~0~0.0~0.0~0.0
[解决办法]
2.24~25628.28
[解决办法]
2.26~193660.37
[解决办法]
2.28~569704.62'
declare @string2 varchar(8000) = '25422~0~0.0~0.0~0.0
[解决办法]
2.82~590.05
[解决办法]
2.84~330.38
[解决办法]
2.88~153.32'
declare @string3 varchar(8000) = '58805~0~0.0~0.0~0.0
[解决办法]
3.35~638.04
[解决办法]
3.4~893.91
[解决办法]
3.45~27770.75'
select @string1 =REPLACE(stuff(@string1,1,CHARINDEX('
[解决办法]
',@string1)-1,''),'
[解决办法]
','
[解决办法]
'+LEFT(@string1,CHARINDEX('~',@string1)))
select @string2 =REPLACE(stuff(@string2,1,CHARINDEX('
[解决办法]
',@string2)-1,''),'
[解决办法]
','
[解决办法]
'+LEFT(@string2,CHARINDEX('~',@string2)))
select @string3 =REPLACE(stuff(@string3,1,CHARINDEX('
[解决办法]
',@string3)-1,''),'
[解决办法]
','
[解决办法]
'+LEFT(@string3,CHARINDEX('~',@string3)))
select col1 as SelectionId,col2 as PriceTag,col3 as PriceValuefrom dbo.fn_MutiSplitTSQL (@string1,'
[解决办法]
','~')
union all select col1, col2, col3 from dbo.fn_MutiSplitTSQL (@string2,'
[解决办法]
','~')
union all select col1, col2, col3 from dbo.fn_MutiSplitTSQL (@string3,'
[解决办法]
','~')
/*
SelectionId PriceTag PriceValue
----------- ---- --------
56343 2.24 25628.28
56343 2.26 193660.37
56343 2.28 569704.62
25422 2.82 590.05
25422 2.84 330.38
25422 2.88 153.32
58805 3.35 638.04
58805 3.4 893.91
58805 3.45 27770.75
(9 行受影响)
*/