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

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 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.

Receive your free copy today!


 

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!

1 Comment on “Using a CTE in a Function to Split Up a Database Connect String

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

Leave a Reply

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

*