I am passing one string to store procedure : 1:20,2:30,4:50
It contains id and appropriate value for it.
how can I add value as shown in below table in database.
ID Value
1 20
2 30
4 50
I have already "stringSplit" function which works perfectly and gives out put in row value some think like this :
1:20
2:30
4:50
can anyone please help me to insert data into table with any solution.
i already try this solution
insert <table> (colname)
select y.item
from dbo.SplitString(@testString, ':') x
cross apply
dbo.SplitString(x.item, ',') y
but this will return duplicate value as more as id value.
my store procedure is
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
As you already splitted into rows and you want insert into some table by splliting into two columns may be 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
You can also try XML.nodes()
and string functions to spit the data. Something like this
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With