Split String with a delimiter in SQL Server using User Defined Function:
Split String with delimiter in SQL Server: User Defined Function plays an important role to perform complex logic.The main benefit of using User Defined Function instead of depending on the SQL Server built-in function we can write our own function.You can learn more about Built-In Functions In SQL Server from our previous blog.
Assume we have some delimited value.Our requirement is to split the value to get the splitted value.So here I am creating a User Defined Function (UDF) in SQL Server to fulfill our requirement.We can also create Stored Procedure to implement this type of functionality, but the problem is that we can not pass Stored Procedure in where and select statement, User Defined Function is suitable for such scenario.You can learn more about Stored Procedure from Stored Procedure in SQL Server.
Here is the script for the same.
CREATE FUNCTION [dbo].[udfSplitString] ( @pValue NVARCHAR(MAX), @pDelimiter NVARCHAR(255) ) RETURNS @SplittedValue TABLE (Item NVARCHAR(4000)) WITH SCHEMABINDING AS BEGIN DECLARE @ll INT = LEN(@pValue) + 1, @ld INT = LEN(@pDelimiter); WITH a AS ( SELECT [start] = 1, [end] = COALESCE(NULLIF(CHARINDEX(@pDelimiter, @pValue, 1), 0), @ll), [value] = SUBSTRING(@pValue, 1, COALESCE(NULLIF(CHARINDEX(@pDelimiter, @pValue, 1), 0), @ll) - 1) UNION ALL SELECT [start] = CONVERT(INT, [end]) + @ld, [end] = COALESCE(NULLIF(CHARINDEX(@pDelimiter, @pValue, [end] + @ld), 0), @ll), [value] = SUBSTRING(@pValue, [end] + @ld, COALESCE(NULLIF(CHARINDEX(@pDelimiter, @pValue, [end] + @ld), 0), @ll)-[end]-@ld) FROM a WHERE [end] < @ll ) INSERT @SplittedValue SELECT [value] FROM a WHERE LEN([value]) > 0 OPTION (MAXRECURSION 0); RETURN; END
You can find your User Defined Function at the following location.See the below image.
For executing the above function you need to write following script.
SELECT * FROM [dbo].[udfSplitString](‘.Net,PHP,Android,Java’,’,’)
You can see from above script the function that takes two parameters first contains the string that we have to split and second one is the delimiter.
After executing above script the output will be.
Hope you have enjoyed this article. Please provide your valuable comment.