Using a CTE in a Function to Split Up a Database Connect String

I came across this example when preparing my CTE presentation for SQL Saturday in Portland OR in 2 weeks.

CREATE FUNCTION dbo.SplitConnectString(@s varchar(8000))
RETURNS table
AS
RETURN (
 WITH splitter_cte AS (
 SELECT CHARINDEX(';', @s) as pos, 0 as lastPos
 UNION ALL
 SELECT CHARINDEX(';', @s, pos + 1), pos
 FROM splitter_cte
 WHERE pos > 0
 ),
 pair_cte AS (
 SELECT chunk,
 CHARINDEX('=', chunk) as pos
 FROM (
 SELECT SUBSTRING(@s, lastPos + 1,
 case when pos = 0 then 80000
 else pos - lastPos -1 end) as chunk
 FROM splitter_cte) as t1
 )
 SELECT substring(chunk, 0, pos) as keyName,
 substring(chunk, pos+1, 8000) as keyValue
 FROM pair_cte
);
GO
declare @connectString varchar(2048)
set @connectString = 'server=myserver;user id=sa;password=asdfasdfasdasdffjfjfj';
SELECT *
 FROM dbo.SplitConnectString(@connectString)
OPTION(MAXRECURSION 0);

Which produces the following output.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!