Separate value pairs and create a table using UDF

Question

I am trying to write a Table-Valued function that takes pairs of values ​​as a parameter and returns a table with two columns.

Below is the signature of the function I'm trying to do.

FUNCTION [dbo].[ValuePairParser]( @DelimitedValuePairs VARCHAR(MAX), @Delimiter CHAR(1), @ValuePairDelimiter CHAR(1) ) RETURNS @ValuePairTable TABLE ( Id INT, Code INT ) 

I want to call a method as shown below

 @ValuePairs VARCHAR(MAX) = '1:1, 1:2, 1:4, 2:3, 1000:230, 130:120,' ValuePairParser (@ValuePairs, ',', ':') 

Can you see any good way to split up ValuePairs complaints above and create a table with two columns?

Answer 1

 CREATE FUNCTION [dbo].[SplitWithPairs] ( @List NVARCHAR(MAX), @MajorDelimiter VARCHAR(3) = ',', @MinorDelimiter VARCHAR(3) = ':' ) RETURNS @Items TABLE ( Position INT IDENTITY(1,1) NOT NULL, LeftItem INT NOT NULL, RightItem INT NOT NULL ) AS BEGIN DECLARE @Item NVARCHAR(MAX), @LeftItem NVARCHAR(MAX), @RightItem NVARCHAR(MAX), @Pos INT; SELECT @List = @List + ' ', @MajorDelimiter = LTRIM(RTRIM(@MajorDelimiter)), @MinorDelimiter = LTRIM(RTRIM(@MinorDelimiter)); WHILE LEN(@List) > 0 BEGIN SET @Pos = CHARINDEX(@MajorDelimiter, @List); IF @Pos = 0 SET @Pos = LEN(@List) + LEN(@MajorDelimiter); SELECT @Item = LTRIM(RTRIM(LEFT(@List, @Pos - 1))), @LeftItem = LTRIM(RTRIM(LEFT(@Item, CHARINDEX(@MinorDelimiter, @Item) - 1))), @RightItem = LTRIM(RTRIM(SUBSTRING(@Item, CHARINDEX(@MinorDelimiter, @Item) + LEN(@MinorDelimiter), LEN(@Item)))); INSERT @Items(LeftItem, RightItem) SELECT @LeftItem, @RightItem; SET @List = SUBSTRING(@List, @Pos + LEN(@MajorDelimiter), DATALENGTH(@List)); END RETURN; END GO DECLARE @ValuePairs VARCHAR(MAX) = '1:1, 1:2, 1:4, 2:3,1000:230, 130:120,'; SELECT LeftItem, RightItem FROM dbo.SplitWithPairs(@ValuePairs, ',', ':') ORDER BY Position; GO 

Answer 2

 create function ValuePairParser(@DelimitedValuePairs varchar(MAX), @Delimiter char(1), @ValuePairDelimiter char(1)) returns @ValuePairTable table(Id int, Code int) as begin with Split(ValuePair, Rest) as ( select left(@DelimitedValuePairs, charindex(@Delimiter, @DelimitedValuePairs)-1), stuff(@DelimitedValuePairs, 1, charindex(@Delimiter, @DelimitedValuePairs), '') where charindex(@Delimiter, @DelimitedValuePairs) > 0 union all select left(Rest, charindex(@Delimiter, Rest)-1), stuff(Rest, 1, charindex(@Delimiter, Rest), '') from Split where charindex(@Delimiter, Rest) > 0 ) insert into @ValuePairTable select left(ValuePair, charindex(@ValuePairDelimiter, ValuePair)-1), stuff(ValuePair, 1, charindex(@ValuePairDelimiter, ValuePair), '') from Split option (maxrecursion 0) return end