Click here to Skip to main content
15,919,749 members
Please Sign up or sign in to vote.
4.00/5 (2 votes)
Hi all,
I have 2 semicolon seperated string passed to a stored procedure along with other variables with simple values.
These semicolon seperated values actually needs to be split and inserted as different rows.
Both the semicolon seperated values are not interrelated and hence can be inserted together into 2 different fields in any order.The spliting is done by using
SQL
select CAST(DATA AS INT) as id  from SplitString('30:40:50',':')

but i want to combine both these splits such that i could pass them together into single insert statements.


for example,
I have string1 '30:40:50' and string2 '23,58,48,60' and some values @id=101,datecreated...

Now i want to insert this values into a table that would look like this,

101      30      23      Jun 25 2013 12:22PM
101      40      58      Jun 25 2013 12:22PM
101      50      48      Jun 25 2013 12:22PM
101      null    60      Jun 25 2013 12:22PM

Any of the string could be longer or smaller or equal.

I am unable to figure out proper way of doing this


Thanks in advance
Posted

May this helps

CREATE TABLE MainTable(Id INT,StringVal1 INT,StringVal2 INT,DateVal DATE)
GO

CREATE PROCEDURE uspTest
        @Id INT,
	@String1 VARCHAR(MAX),
	@SplitChar1 CHAR,
	@String2 VARCHAR(MAX),
	@SplitChar2 CHAR,
	@DateVal DATE
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @XmlString XML
    
	CREATE TABLE #Value(Id INT IDENTITY,Val INT)
	SELECT @XmlString = 
	CONVERT(xml,'<root><s>' + REPLACE(@String1,@SplitChar1,'</s><s>') + '</s></root>')
	
	INSERT #Value(Val)
	SELECT A.Value FROM
	(
	SELECT [Value] = T.c.value('.','VARCHAR(20)') FROM @XmlString.nodes('/root/s') T(c)
	)A
	
	CREATE TABLE #Value1(Id1 INT IDENTITY,Val1 INT)
	SELECT @XmlString = 
	CONVERT(xml,'<root><s>' + REPLACE(@String2,@SplitChar2,'</s><s>') + '</s></root>')
    
        INSERT #Value1(Val1)
	SELECT A.Value FROM
	(
	SELECT [Value] = T.c.value('.','VARCHAR(20)') FROM @XmlString.nodes('/root/s') T(c)
	)A 
	
	SELECT * INTO #NewTab
	FROM
	(
	  SELECT V.Val,V1.Val1 FROM #Value V FULL OUTER JOIN #Value1 V1 ON V1.Id1 = V.Id
	)A

	INSERT MainTable(StringVal1,StringVal2)
	SELECT * FROM #NewTab
	
	UPDATE MainTable SET Id = @Id
	UPDATE MainTable SET DateVal = @DateVal	
		
	DROP TABLE #NewTab
	DROP TABLE #Value
        DROP TABLE #Value1
END
GO

EXEC uspTest 101,'30:40:50',':','23,58,48,60',',','2013-06-25'
GO  
SELECT * FROM MainTable



Id	StringVal1	StringVal2	DateVal
101	30		23		2013-06-25
101	40		58		2013-06-25
101	50		48		2013-06-25
101	NULL		60		2013-06-25
 
Share this answer
 
v5
Comments
RedDk 25-Jun-13 11:03am    
Both a great question and a great answer.
Maciej Los 26-Jun-13 15:29pm    
Good job, +5!
shivprasads 27-Jun-13 1:51am    
Thanks a lot Radhadeep12 .
That was completely helpful.


Though I kept the initial approach same, using both the string as semi-colon seperated and creating 2 temp tables from them.
using

INSERT #Temptablename(Column)
SELECT CAST(DATA AS INT) from SplitString('30:50:60',':')

From there on it was quite simple to insert them along with some other values to the appropriate table as u had already posted.

Thanks a ton again.
Another solution is to use Common Table Expressions[^].

Have a look here:
SQL
DECLARE @string1 VARCHAR(30)
DECLARE @string2 VARCHAR(30)
DECLARE @id INT
DECLARE @curDate DATETIME

SET @string1 ='30:40:50' 
SET @string2 ='23,58,48,60'
SET @id = 101
SET @curDate = GETDATE()

--destination table
DECLARE @tmp TABLE (ID INT, Val1 INT, Val2 INT, aDate DATETIME)

--splited values of string1
DECLARE @fv TABLE (ID INT, Val1 INT, aDate DATETIME, RowNo INT)
;WITH firstVal AS 
(
	SELECT @id AS ID, LEFT(@string1, CHARINDEX(':',@string1)-1) AS Val1, CONVERT(VARCHAR(30),RIGHT(@string1, LEN(@string1)-CHARINDEX(':',@string1))) AS Remainder, 1 AS RowNo
	WHERE CHARINDEX(':',@string1)>0
	UNION ALL
	SELECT @id AS ID, LEFT(Remainder, CHARINDEX(':',Remainder)-1) AS Val1, CONVERT(VARCHAR(30),RIGHT(Remainder, LEN(Remainder)-CHARINDEX(':',Remainder))) AS Remainder, RowNo + 1 AS RowNo
	FROM firstVal
	WHERE CHARINDEX(':',Remainder)>0
	UNION ALL
	SELECT @id AS ID, Remainder AS Val1, NULL AS Remainder, RowNo + 1 AS RowNo
	FROM firstVal
	WHERE CHARINDEX(':',Remainder)=0
)
INSERT INTO @fv (ID, Val1, aDate, RowNo)
SELECT ID, Val1, @curDate AS aDate, RowNo
FROM firstVal 

--splited values of string2
DECLARE @sv TABLE (ID INT, Val2 INT, aDate DATETIME, RowNo INT)
;WITH secondVal AS
(
	SELECT @id AS ID, LEFT(@string2, CHARINDEX(',',@string2)-1) AS Val2, CONVERT(VARCHAR(30), RIGHT(@string2, LEN(@string2)-CHARINDEX(',',@string2))) AS Remainder, 1 AS RowNo
	WHERE CHARINDEX(',',@string2)>0
	UNION ALL
	SELECT @id AS ID, LEFT(Remainder, CHARINDEX(',',Remainder)-1) AS Val2, CONVERT(VARCHAR(30), RIGHT(Remainder, LEN(Remainder)-CHARINDEX(',',Remainder))) AS Remainder, RowNo +1 AS RowNo
	FROM secondVal
	WHERE CHARINDEX(',',Remainder)>0
	UNION ALL
	SELECT @id AS ID, Remainder AS Val2, NULL AS Remainder, RowNo + 1 AS RowNo
	FROM secondVal
	WHERE CHARINDEX(',',Remainder)=0
)
INSERT INTO @sv (ID, Val2, aDate, RowNo)
SELECT ID, Val2, @curDate AS aDate, RowNo
FROM secondVal 

--get count of splited values
DECLARE @fvc INT
DECLARE @svc INT

SELECT @fvc = COUNT(ID)
FROM @fv

SELECT @svc = COUNT(ID)
FROM @sv

--SELECT @fvc AS CountOfFirstValues, @svc AS CountOfSecondValues

IF (@fvc > @svc)
	BEGIN
		INSERT INTO @tmp (ID, Val1, Val2, aDate)
		SELECT t1.ID, t1.Val1, t2.Val2, t1.aDate
		FROM @fv AS t1 LEFT JOIN @sv AS t2 ON t1.ID = t2.ID AND t1.RowNo = t2.RowNo
	END
ELSE
	BEGIN
		INSERT INTO @tmp (ID, Val1, Val2, aDate)
		SELECT t2.ID, t1.Val1, t2.Val2, t2.aDate
		FROM @fv AS t1 RIGHT JOIN @sv AS t2 ON t1.ID = t2.ID AND t1.RowNo = t2.RowNo
	END

SELECT *
FROM @tmp


Result:
101   30      23    2013-06-26 21:22:02.763
101   40      58    2013-06-26 21:22:02.763
101   50      48    2013-06-26 21:22:02.763
101   NULL    60    2013-06-26 21:22:02.763
 
Share this answer
 
Comments
shivprasads 27-Jun-13 1:53am    
thanks for the reply,
this was indeed helpful but was a bit tricky to understand and more complex.
anyways thanks again for the efforts
Maciej Los 27-Jun-13 2:05am    
You're welcome ;)
Did you vote for 3 stars? If yes, i would like to tell you that means down-voting. I assume that my answer was helpful, so... Can you be so kind and re-vote for 4? And finally, can you accept my answer (green button) as a solution?
shivprasads 27-Jun-13 3:29am    
Alright it was indeed helpful and i revoted it to 4 but actually i would like to say that the solution posted by Radhadeep12 was more apt and so i accepted it as answer.
Maciej Los 27-Jun-13 4:16am    
Thank you ;)

Don't afraid to accept all helpful answers as solutions ;)
Formerly it is a solution, even if it's a bit more complex. Right?
Hi,

Consider your string as,

string1 '30:40:50' and string2 '23,58,48,60,70'

Pass the string value like string1 '30:40:50::60' and string2 '23,58,48,60,82'
When splitting it will take corresponding value without confusion.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900