Split values based on deleimiter in SQL Server

In Some scenarios we need to split a string value based on the delimiter value so if we have a table which contains string values in one column and we need to split them based on delimiter value or in SSRS we have a parameter value which is in form of coming comma separated value then we can designed a user defined function which will be accepting string value with delimiter to split those given values.

 

So here I have designed a split function to split all strings based on the delimiter value to return as a object as:

 

GO

 

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE FUNCTION [dbo].[UDF_Split_String_To_Table] (

   @StringVal VARCHAR(max), /* Variable to accept input string which need to split absed on the delimiter*/

   @Delimeter char(1),   /* Variable to accept delimiter type*/

   @TrimSpace bit )      /* To kill whitespace? */

RETURNS @ResultTable TABLE ( [Value] VARCHAR(4000) )

AS

BEGIN

    DECLARE @ResVal    VARCHAR(4000)

    WHILE LEN(@StringVal) > 0

    BEGIN

        SET @ResVal    = LEFT(@StringVal,

             ISNULL(NULLIF(CHARINDEX(@Delimeter, @StringVal) - 1, -1),

             LEN(@StringVal)))

        SET @StringVal = SUBSTRING(@StringVal,

             ISNULL(NULLIF(CHARINDEX(@Delimeter, @StringVal), 0),

             LEN(@StringVal)) + 1, LEN(@StringVal))

  IF @TrimSpace = 1 Set @ResVal = LTRIM(RTRIM(@ResVal))

    INSERT INTO @ResultTable ( [Value] )

        VALUES ( @ResVal )

    END

    RETURN

END

 

GO

 

If you want to split a string value, based on the delimiter and want to show each value in a separate column than go through this post:Split function to show delimiter separated value in separate columns