在使用my函数之前,您需要设置一个“辅助”表,每个数据库只需要执行一次:
CREATE TABLE Numbers
(Number int NOT NULL,
CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
SET @x=@x+1
INSERT INTO Numbers VALUES (@x)
END
使用此函数拆分字符串,它不会循环,速度非常快:
CREATE FUNCTION [dbo].[FN_ListToTable]
(
@SplitOn char(1) --REQUIRED, the character to split the @List string on
,@List varchar(8000) --REQUIRED, the list to split apart
)
RETURNS
@ParsedList table
(
ListValue varchar(500)
)
AS
BEGIN
/**
Takes the given @List string and splits it apart based on the given @SplitOn character.
A table is returned, one row per split item, with a column name "ListValue".
This function workes for fixed or variable lenght items.
Empty and null items will not be included in the results set.
Returns a table, one row per item in the list, with a column name "ListValue"
EXAMPLE:
----------
SELECT * FROM dbo.FN_ListToTable(',','1,12,123,1234,54321,6,A,*,|||,,,,B')
returns:
ListValue
-----------
1
12
123
1234
54321
6
A
*
|||
B
(10 row(s) affected)
**/
----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
INSERT INTO @ParsedList
(ListValue)
SELECT
ListValue
FROM (SELECT
LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
FROM (
SELECT @SplitOn + @List + @SplitOn AS List2
) AS dt
INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
WHERE SUBSTRING(List2, number, 1) = @SplitOn
) dt2
WHERE ListValue IS NOT NULL AND ListValue!=''
RETURN
END --Function FN_ListToTable
select
a.Col1,b.Col2,a.Col2,b.Col2
FROM TableA a
INNER JOIN TableB b On b.Col2 IN (SELECT ListValue FROM dbo.FN_ListToTable(',',a.Col2))
输出
Col1 Col2 Col2
----------- ------------- -------------------------------
2222 0006AG_0042 0006AG_0042
2343 0032BQ 0032BP, 0032BQ, 0032BR
1232 5000_0709 5000_0708, 5000_0709, 5000_0710
1232 5000_0710 5000_0708, 5000_0709, 5000_0710
编辑
select
a.Col1,b.Col2,a.Col2
FROM TableA a
CROSS APPLY dbo.FN_ListToTable(',',a.Col2) a2
INNER JOIN TableB b On a2.ListValue=b.Col2
Create Table TableA2(Col1 int , Col2 varchar(50) )
INSERT INTO TableA2
SELECT
a.Col1, b.ListValue
FROM TableA a
CROSS APPLY dbo.FN_ListToTable(',',a.Col2) b
SELECT
a.*
FROM TableA2 a
INNER JOIN TableB b ON a.Col2=b.Col2
要完成更改,如果需要,可以删除表TableA,然后将表A2重命名为表A
