How to insert two split columns into two columns of table

Question

1.00/5 (1 vote)

See more:

i have a function which will take a string and a character as input and it will split string by this character and returns the column

SQL

DECLARE @strIndustryID varchar(MAX)='1,2,3,4,5,6,7,44,55,66,77,88'
DECLARE	@strIndustryDescription varchar(MAX)='desc1|desc2|desc3|desc4|desc5|desc6|desc7|desc44|desc55|desc66|desc77|desc88'
DECLARE @COUNT INT=0

SELECT * FROM dbo.Split(@strIndustryID,',');

SELECT * FROM dbo.Split(@strIndustryDescription,'|');

and output by first Query

VB

and from second query

VB

desc1
desc2
desc3
desc4
desc5
desc6
desc7
desc44
desc55
desc66
desc77
desc88

now i want output like

XML

<pre lang="vb">desc1
Col1 col2
2   desc2
3   desc3
4   desc4
5   desc5
.
.
.
.

and i want to insert these column into the table1 which has a coulm named col1 and col2

How can i Do this?

Posted 12-Apr-13 1:44am

kishanthakar

Updated 12-Apr-13 1:46am

v2

Add a Solution

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Archit Patel · Answer 1 · 2013-04-12T02:38:00

SQL

<pre lang="sql">DECLARE @strIndustryID varchar(MAX)='1,2,3,4,5,6,7,44,55,66,77,88'
DECLARE @strIndustryDescription varchar(MAX)='desc1|desc2|desc3|desc4|desc5|desc6|desc7|desc44|desc55|desc66|desc77|desc88'
DECLARE @COUNT INT=0
SELECT a, b  FROM
(
SELECT a FROM dbo.Split(@strIndustryID,',') t1 CROSS JOIN
SELECT b FROM dbo.Split(@strIndustryDescription,'|') t2
) AS main
WHERE a = REPLACE(b, 'desc', '')

*Karthik Harve* · Answer 2 · 2013-04-12T02:13:00

Hi,

try like below.

SQL

DECLARE @strIndustryID varchar(MAX)='1,2,3,4,5,6,7,44,55,66,77,88'
DECLARE	@strIndustryDescription varchar(MAX)='desc1|desc2|desc3|desc4|desc5|desc6|desc7|desc44|desc55|desc66|desc77|desc88'
DECLARE @COUNT INT=0

CREATE TABLE #tbTemp1
{
    sl INT,
    value VARCHAR(100)
}

CREATE TABLE #tbTemp2
{
    sl INT,
    value VARCHAR(100)
} 

INSERT INTO #tbTemp1
SELECT ROW_NUMBER() OVER(ORDER BY col1 asc) 'sl', col1 FROM dbo.Split(@strIndustryID,',');

INSERT INTO #tbTemp2
SELECT ROW_NUMBER() OVER(ORDER BY col1 asc) 'sl', col2 FROM dbo.Split(@strIndustryDescription,'|');

SELECT A.value, B.value FROM #tbTemp1 as A INNER JOIN #tbTemp2 as B ON A.sl = B.sl

DROP TABLE #tbTemp1
DROP TABLE #tbTemp2

hope it works.

kishanthakar · Answer 3 · 2013-04-12T02:11:00

Solution 3

Thanks bhushan and shanalal i was trying to do the same but unable to do so. :-)

Posted 12-Apr-13 2:11am

kishanthakar

Shanalal Kasim · Answer 4 · 2013-04-12T01:56:00

Hi,

Change your splitter function like below

SQL

CREATE FUNCTION [dbo].[Split]
(
	@RowData nvarchar(max),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	id int identity(1,1),
	Data nvarchar(100)
) 
AS  
BEGIN 
	Declare @Cnt int
	Set @Cnt = 1
 
	While (Charindex(@SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
 
		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
		Set @Cnt = @Cnt + 1
	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))
 
	Return
END

Then use below sql

SQL

select a.data,b.data from dbo.[Split](@strIndustryID,',') a, dbo.[Split](@strIndustryDescription,'|') b
where a.id = b.id

Bhushan Shah1988 · Answer 5 · 2013-04-12T01:56:00

First of all change your split function as shown below. It will return Identity column as well as values.

SQL

CREATE FUNCTION  [dbo].[Split]
(  
	@RowData VARCHAR(MAX),  
	@SplitOn NVARCHAR(5)
)    
RETURNS @RtnValue TABLE
(  
	Id		INT IDENTITY(1,1),  
	Data	VARCHAR(MAX)  
)   
AS    
BEGIN   
	DECLARE @Cnt INT
	SET		@Cnt = 1  
  
	WHILE (CHARINDEX(@SplitOn,@RowData)>0)
	BEGIN
		INSERT INTO @RtnValue (data)
		SELECT Data = LTRIM(RTRIM(SUBSTRING(@RowData,1,CHARINDEX(@SplitOn,@RowData)-1)))  
  
		SET @RowData = SUBSTRING(@RowData,CHARINDEX(@SplitOn,@RowData)+1,LEN(@RowData))
		SET @Cnt = @Cnt + 1
	END  
   
	INSERT INTO @RtnValue (data)
	SELECT Data = LTRIM(RTRIM(@RowData))  
RETURN
END

after that execute following block :

SQL

DECLARE @strIndustryID varchar(MAX)='1,2,3,4,5,6,7,44,55,66,77,88'
DECLARE	@strIndustryDescription varchar(MAX)='desc1|desc2|desc3|desc4|desc5|desc6|desc7|desc44|desc55|desc66|desc77|desc88'
DECLARE @COUNT INT=0

CREATE TABLE #TEMP1
(
	ID	INT,
	strVal VARCHAR(50)
)
INSERT	INTO #TEMP1
SELECT	* FROM dbo.Split(@strIndustryID,',');

CREATE TABLE #TEMP2
(
	ID	INT,
	strVal VARCHAR(50)
)
INSERT	INTO #TEMP2 
SELECT * FROM dbo.Split(@strIndustryDescription,'|');

INSERT  INTO Table1(COL1,COL2)
SELECT	T1.strVal,t2.strVal 
FROM	#TEMP1 T1
INNER JOIN #TEMP2 T2 ON T1.ID = T2.ID

DROP TABLE #TEMP2
DROP TABLE #TEMP1

How to insert two split columns into two columns of table

5 solutions

Solution 5

Solution 4

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0