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

Download PDF

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:

SteveStedman5
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!

Leave a Reply

Your email address will not be published. Required fields are marked *

*