split text in stored procedure
How to split text in stored procedure.
CREATE PROCEDURE
(
@A开发者_如何学JAVArrayList nvarchar(1000)
)
BEGIN
--I want to split @ArrayList. That is contains string. ex '1,2,3,4,5,6...'
END
declare @ArrayList nvarchar(1000) = '1,2,3,4,5,6'
declare @XMLList xml
set @XMLList = cast('<I>'+replace(@ArrayList, ',', '</I><I>')+'</I>' as xml)
select
I.value('.', 'int') as Value
from @XMLList.nodes('I') as I(I)
Use should use XML PATH
method as below :
SELECT STUFF(
(SELECT TOP 10 ',' + STOCKNO FROM ItemMaster FOR XML PATH('')), 1, 1, ''
) as Data
Result:
Data
150200006,150200010,150200014,150200018,150200021,150200025,150400078,150400082,150200005,150200009
精彩评论