Split Function

In SQL Server there is no in-built split function. But we could create our own Split function and use.

Following is the script for Split function.

CREATE FUNCTION [dbo].[SPLIT] 

(

    @String NVARCHAR(MAX), --String need to be Split

    @Delimiter CHAR(1), --Demiliter

    @Index integer --Index of the Splited String

)

RETURNS VARCHAR(200)

AS

BEGIN

      -- Declare the return variable here

      DECLARE  @Start INT,  @End INT ,  @Count int,  @Value varchar(500)

      -- Add the T-SQL statements to compute the return value here

      SELECT  @Start = 1,  @End = CHARINDEX(@Delimiter,  @String)

      SET  @Count =1

      WHILE  @Start < LEN( @String) + 1 BEGIN

        IF  @End = 0 

            SET  @End = LEN( @String) + 1

      

        IF  @Count = @Index         

        set  @Value = (SUBSTRING( @String,  @Start,  @End -  @Start))        

        SET  @Start =  @End + 1

        SET  @End = CHARINDEX(@delimiter,  @String,  @Start)

        SET  @Count =  @Count + 1

      END

      -- Return the result of the function

      RETURN  @Value

END

Function will take following arguments

1. String: The string need to be split.

2. Delimiter: delimiter used to split the string.

3. Index: which index of value to be returned.

Example:

 I hope this article will be very helpful to all. Thanks for reading this article.

“Keep reading and share the knowledge”  

“Grow more trees to save the Earth”