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,’;’)