如何将两个拆分列插入两列表中

问题描述

我有一个函数，它将字符串和字符作为输入，它将按此字符分割字符串并返回列

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

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

1

2
3
4
5
6
7
44
55
66
77
88

和第二次查询

and from second query

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

现在我想输出像

now i want output like

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

我想插入这些table1中的列有一个名为col1和col2的coulm



我怎么能这样做？

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

How can i Do this?

推荐答案

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

然后使用在sql

Then use below sql

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

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.

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 :

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

这篇关于如何将两个拆分列插入两列表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！