Insert multiple values ​​into a table with string input

Question

I pass one line to store the procedure: 1:20,2:30,4:50 It contains id and the corresponding value for it.

how can i add a value as shown in the table below in the database.

 ID Value 1 20 2 30 4 50 

I already have a function "stringSplit", which works fine and produces a value in a string. Some people think so:

 1:20 2:30 4:50 

Can someone help me insert data into a table with any solution.

I'm already trying this solution

 insert <table> (colname) select y.item from dbo.SplitString(@testString, ':') x cross apply dbo.SplitString(x.item, ',') y 

but this will return a duplicate value as the id value.

my store procedure

 CREATE PROCEDURE [dbo].[temp_result_insert] @dataString varchar(max) AS insert into tempTable(id,marks) select x.Item,y.Item from dbo.SplitStringVarcahr(@dataString, ':') x cross apply dbo.SplitStringVarcahr(x.Item,',') y RETURN 0 

Answer 1

Since you are already split into rows and want to insert into any table, split into two columns, maybe this works

 CREATE TABLE #Test(ID INT,Val INT) declare @t table (val varchar(50)) insert into @t (val)values ('1:20,2:30,4:50') declare @str varchar(max) ;with cte as ( SELECT Split.a.value('.', 'VARCHAR(100)') AS String FROM (SELECT CAST ('<M>' + REPLACE([val], ',', '</M><M>') + '</M>' AS XML) AS String FROM @t) AS A CROSS APPLY String.nodes ('/M') AS Split(a)) INSERT INTO #Test select SUBSTRING(String,0,CHARINDEX(':',String)),REVERSE(SUBSTRING(reverse(String),0,CHARINDEX(':',reverse(String)))) from cte select * from #test 

Answer 2

You can also try XML.nodes() and string functions to spit on data. Something like that

 DECLARE @var VARCHAR(100) = '1:20,2:30,4:50' DECLARE @xml xml = CONVERT(xml, '<r>' + REPLACE(@var,',','</r><r>') + '</r>') SELECT LEFT(val,cindex - 1) c1,RIGHT(val,LEN(val) - cindex) c2 FROM ( SELECT CHARINDEX(':',c.value('text()[1]','VARCHAR(100)')) cindex,c.value('text()[1]','VARCHAR(100)') val FROM @xml.nodes('r') as t(c))c 

Answer 3

Use substring and charindex:

 SELECT Substring(col, 0, Charindex(col, ':') - 1) AS id, Substring(col, Charindex(col, ':') + 1, Len(col)-Charindex(col, ':')) AS value FROM splittedtable