我有一个存储过程,有2个输入参数类型为字符串(varchar(max))。
@draw = '172, 182, 167'
@game = '100, 101, 100'
我想解析并将它们存储在表中,如下所示:
|----------------------------------|
| draw | game |
|----------------------------------|
| 172 | 100 |
| 182 | 101 |
| 167 | 100 |
|----------------------------------|
我怎样才能做到这一点?如有任何建议,不胜感激,谢谢。
这是我到目前为止所尝试的:我能够将字符串分成两行,但我无法弄清楚的部分是我如何解析2个不同的输入,并将它们以同步的方式放入我的表中,因为它们被传递到过程中。
这是我试图解析它们:
create table report_game_draw (
draw_id varchar(max) NULL,
game_id varchar(max) NULL
)
go
Declare @draw nvarchar(MAX)
Declare @game nvarchar(MAX)
Set @draw ='188,189,190,191,192,193,194'
Set @game ='188,189,190,191,192,193,194'
DECLARE @XML AS XML
DECLARE @XML1 AS XML
DECLARE @Delimiter AS CHAR(1) =','
SET @XML = CAST(('<X>'+REPLACE(@draw,@Delimiter ,'</X><X>')+'</X>') AS XML)
SET @XML1 = CAST(('<X>'+REPLACE(@game,@Delimiter ,'</X><X>')+'</X>') AS XML)
INSERT INTO report_game_draw (draw_id,game_id)
((SELECT N.value('.', 'INT') AS ID FROM @XML.nodes('X') AS T(N),
(SELECT N.value('.', 'INT') AS ID FROM @XML1.nodes('X') AS T(N))
有很多方法可以做到这一点——请注意,尽管SQL Server提供了string_split函数,但它只在Azure中可靠,因为Azure支持序列号来保证排序。
对于这种类型的过程,我倾向于使用Json功能,如下所示:declare
@draw varchar(max) = '172, 182, 167',
@game varchar(max) = '100, 101, 100';
select Draw, Game
from (
select Convert(int, j.[value]) as Draw, 1 + Convert(tinyint,j.[key]) Seq
from OpenJson(Concat('["',replace(@draw,',', '","'),'"]')) j
) d join (
select Convert(int, j.[value]) as Game, 1 + Convert(tinyint,j.[key]) Seq
from OpenJson(Concat('["',replace(@game,',', '","'),'"]')) j
) g on d.Seq = g.Seq
例子小提琴