Create the function in msdb or somewhere else.
CREATE FUNCTION dbo.SplitTwoStringsWithSameOrder
(
@List1 varchar(50),
@List2 varchar(50),
@Delim varchar(10)
)
RETURNS TABLE
AS
RETURN
(
WITH src(r) AS
(
SELECT 1 UNION ALL SELECT r + 1 FROM src WHERE r < 10
),
Numbers(Number) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM src AS s1, src AS s2 -- add more if you need longer strings
),
parsed(s1,s2,r1,r2)
AS
(
SELECT
SUBSTRING(@List1, n1.Number, CHARINDEX(@Delim, @List1
+ @Delim, n1.Number) - n1.Number),
SUBSTRING(@List2, n2.Number, CHARINDEX(@Delim, @List2
+ @Delim, n2.Number) - n2.Number),
r1 = ROW_NUMBER() OVER (ORDER BY n1.Number),
r2 = ROW_NUMBER() OVER (ORDER BY n2.Number)
FROM Numbers AS n1, Numbers AS n2
ON n1.Number <= LEN(@List1)
AND n2.Number <= LEN(@List2)
AND SUBSTRING(@Delim + @List1, n1.Number, LEN(@Delim)) = @Delim
AND SUBSTRING(@Delim + @List2, n2.Number, LEN(@Delim)) = @Delim
)
SELECT s1, s2, r1, r2 FROM parsed WHERE r1 = r2
);
Then, as @gbn noted, reference it by 3-part name wherever your query has to run.
CREATE TABLE #temp
(id INT,
keys VARCHAR(50),
vals VARCHAR(50)
);
INSERT INTO #temp
VALUES
(1, '1,2,3', 'one,two,three'),
(2, '4,5,6', 'four,five,six'),
(3, '7,8,9', 'seven,eight,nine');
SELECT t.id, f.s1, f.s2 FROM #temp AS t
CROSS APPLY msdb.dbo.SplitTwoStringsWithSameOrder(keys, vals, ',') AS f
ORDER BY t.id, f.r1;
GO
DROP TABLE #temp;
Results:
The resulting plan, shown in Plan Explorer (disclaimer: I'm the Product Manager), is not the prettiest thing I've ever seen (click to enlarge a little bit):
But there is exactly one scan of #temp (4% cost). The biggest costs are two sorts and a spool, and there is some I/O due to a worktable which I am not sure is avoidable.
If you KNOW you will only ever have 50 characters in either of these strings, then you can get a much simpler plan with a built-in Numbers table (people object to these, but they're very useful, and they are almost always in memory if you reference them enough). This doesn't help I/O but removing the recursive CTE and other constructs of building the numbers inside the function is quite helpful for CPU etc.
First, the numbers table:
DROP TABLE dbo.Numbers;
;WITH n AS
(
SELECT
TOP (50) rn = ROW_NUMBER() OVER
(ORDER BY [object_id])
FROM sys.all_columns
ORDER BY [object_id]
)
SELECT [Number] = rn - 1
INTO dbo.Numbers
FROM n;
CREATE UNIQUE CLUSTERED INDEX n ON dbo.Numbers([Number]);
Then a second version of the function:
CREATE FUNCTION dbo.SplitTwoStringsWithSameOrder2
(
@List1 varchar(50),
@List2 varchar(50),
@Delim nvarchar(10)
)
RETURNS TABLE
AS
RETURN
(
WITH parsed(s1,s2,r1,r2)
AS
(
SELECT
SUBSTRING(@List1, n1.Number, CHARINDEX(@Delim, @List1
+ @Delim, n1.Number) - n1.Number),
SUBSTRING(@List2, n2.Number, CHARINDEX(@Delim, @List2
+ @Delim, n2.Number) - n2.Number),
r1 = ROW_NUMBER() OVER (ORDER BY n1.Number),
r2 = ROW_NUMBER() OVER (ORDER BY n2.Number)
FROM dbo.Numbers AS n1
INNER JOIN dbo.Numbers AS n2
ON n1.Number <= LEN(@List1)
AND n2.Number <= LEN(@List2)
AND SUBSTRING(@Delim + @List1, n1.Number, LEN(@Delim)) = @Delim
AND SUBSTRING(@Delim + @List2, n2.Number, LEN(@Delim)) = @Delim
)
SELECT s1, s2, r1, r2 FROM parsed WHERE r1 = r2
);
GO
Here is the simpler plan that results (again, click to enlarge):
The plan still has two sort operations, but the spool is gone, there is still only one scan of #temp, and in my limited tests the cost numbers (absolute cost numbers, not %) were better every time.
I don't know precisely either of these will scale with a lot more rows, but it's worth testing, and if you weigh this against other solutions and it can't scale well, that may be a point your reconsider the design (store these relationally instead of as comma-separated sets).
