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!
Leave a Reply