Split function in SQL

Unlike Split() function in C#, we don't have any pre-defined in SQL, that splits the string containing the delimiter. While during development, it is sometimes required the functionality of split function similar to that of C# in SQL too.

After a very long time, techiners back again with a new post that will explain the process of creating a function that split the string containing the delimiter, which accepts two parameters- one string and other delimiter.

The first parameter is the string that contains the delimiter and needs to be split across it.

So without much ado, lets start...

Step 1:  We will create a function named as fnSplitString that accepts two paramters. So, parameter will go like this. @string of type NVARCHAR(MAX) and @delimiter of type CHAR(1).

Step 2: Since we need to return the separated strings, hence we specify the return type of this function as TABLE with TWO columns defined as StringOne and StringTwo each of type VARCHAR(MAX).

Step 3: Next we declare two variables each of type INT. These variables are to hold the position of the @start and @end of the string. We start with 1 i.e., the first character of the string and we find the location of the delimiter using CHARINDEX function. This location will stored in @end variable.

If @end is 0, that is delimiter is not found, than we set the @end variable to the LENGTH of the string, in order to return the complete string as it is.

Step 4: Now we fetch the first string using SUBSTRING function passing three arguments to it- string, start, and length. start it the position from which the string is to be fetched and length is the number of characters to fetch from the string from start position.

First string is fetched from start and length is end-start.

Second string is fetched from end-start+2 and length is end.

Step 5: Insert these two strings into the output table that the function will return.

Step 6: Congratulations! You are done. Just execute the function using 

select * from dbo.fnSplitString('techiners.in','.')

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(Exchange VARCHAR(50),Segment VARCHAR(50)) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
        IF @end = 0  
            SET @end = LEN(@string) + 1       
       INSERT INTO @output (Exchange,Segment)  
        VALUES(SUBSTRING(@string, @start, @end - @start),SUBSTRING(@string, @end - @start+2,@End)) 
        SET @end = CHARINDEX(@delimiter, @string, @start)
    RETURN 
END

CONCLUSION



In this post we have seen how to create a function in SQL that offers somewhat similar functionality as Split() function in C#. We have successfully tested the function by executing it.



FEEDBACK



Hope the post will be useful for you in your programming career. What I need from you is to comment below and share with me your doubts and/or suggestions. Thank you :) Have a wonderful programming. Stay Blessed!