Category: SQL Server Tips

Being able to do a simple split on a string in SQL server have always been a real pain.

With SQL Server 2016, Microsoft introduces the new “STRING_SPLIT” function.

Easy to call, but very limited.

select * from string_split('s,p,l,i,t', ',')

returns a 5 row, 1 for each letter. Just values, no position.

That’s why I added some features to that function, with the addition of the “position” in the resultset.

CREATE OR ALTER FUNCTION string_split_position
(
	@input nvarchar(MAX),
	@separator nvarchar(1)
)  
RETURNS @RtnValue table 
(
	[value] nvarchar(max),
	position int identity
) 
AS  
BEGIN 	
	INSERT INTO @RtnValue ([value])
	SELECT [value] FROM string_split(@input, @separator)
	Return
END;
GO
SELECT * FROM string_split_position('s,p,l,i,t', ',')

value	position
s	1
p	2
l	3
i	4
t	5

Finally, I also create an “inline” function, used to get 1-based nth character directly from a string.

CREATE OR ALTER FUNCTION string_split_inline
(
	@input nvarchar(MAX),
	@separator nvarchar(1),
	@position int
)
RETURNS nvarchar(max)
AS
BEGIN
	DECLARE @value nvarchar(max)
	select @value = ssp.[value]
	from string_split_position(@input, @separator) ssp
	where ssp.position = @position
	RETURN @value
END
GO
SELECT dbo.string_split_inline('s,p,l,i,t', ',', 3) AS third
returns 'l'

If you have ideas to make these functions more powerful, do not hesitate to share them with us in the comments.