Inserting multiple value in table with String input

Question

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

mohan111 · Accepted Answer · 2015-04-28 07:44:58Z

7

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

answered Apr 28, 2015 at 7:44

mohan111

8,9054 gold badges35 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ughai · Accepted Answer · 2015-04-28 07:46:33Z

3

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

answered Apr 28, 2015 at 7:46

ughai

9,9004 gold badges33 silver badges48 bronze badges

Comments

knkarthick24 · Accepted Answer · 2015-04-28 07:38:04Z

0

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

answered Apr 28, 2015 at 7:38

knkarthick24

3,24619 silver badges21 bronze badges

Collectives™ on Stack Overflow

Inserting multiple value in table with String input

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related