Using a CTE in a Function to Split Up a Database Connect String
I came across this example when preparing my CTE presentation a while back.
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.
Download my book for FREE when you sign up for my mailing list.
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!
Cool CTE demo. Something like this is an option for SQL 2016 and later:
CREATE FUNCTION dbo.SplitConnectString(@s varchar(8000))
RETURNS TABLE
AS
RETURN
SELECT SUBSTRING(value,0,CHARINDEX(‘=’,value)) as keyName,
SUBSTRING(value,CHARINDEX(‘=’,value)+1,LEN(value)) as keyValue
FROM STRING_SPLIT(@s,’;’)