How to Get Words Separated in a String Concatenated by Delimiter

Introduction

This tip shows you a T/SQL function to split a string concatenated by a delimiter.

Background

Here, you can see an example on how to use the function. The function takes 3 parameters; the first parameter takes string value separated by a delimiter (i.e. comma), the second parameter takes integer value that represents the number of words in a string separated by delimiter and the third parameter takes the delimiter.

Function will return NULL if integer parameter is not in range (i.e. less-than equal to 0 OR greater than count of words separated by a delimiter).

For example: Suppose there is a string MUMBAI,DELHI,KOLKATA,CHENNAI,GOA,UP and we need to get each word.

1) declare @code nvarchar(100)
    set @code=dbo.Split('MUMBAI,DELHI,KOLKATA,CHENNAI,GOA,UP' , 1,',')
    select @code 
        
OUTPUT: MUMBAI 
        
2)declare @code nvarchar(100)
    set @code=dbo.Split('MUMBAI,DELHI,KOLKATA,CHENNAI,GOA,UP' , 3,',')
    select @code
            
OUTPUT: KOLKATA 
            
3)declare @code nvarchar(100)
    set @code=dbo.Split('MUMBAI,DELHI,KOLKATA,CHENNAI,GOA,UP' , 7,',')
    select @code
                
OUTPUT: NULL 

4)declare @code nvarchar(100)
    set @code=dbo.Split('MUMBAI,DELHI,KOLKATA,CHENNAI,GOA,UP' , 0,',')
    select @code
                        
OUTPUT: NULL 

Using the Code

create function Split(@codevar nvarchar(100),@count int,@delimiter char(1))
returns nvarchar(10)
as
begin
    set @codevar=@codevar+@delimiter
    declare @delimposfwd int=0
    declare @delimposbwd int=NULL
    if (LEN(@codevar)-LEN(REPLACE(@codevar,@delimiter,''))<@count or @count<=0) return NULL
    while(@count>0)
        begin
            set @delimposbwd = @delimposfwd
            set @delimposfwd=CHARINDEX(@delimiter,@codevar,@delimposfwd+1)
            set @count=@count-1;
        end
    return SUBSTRING(@codevar,@delimposbwd+1,@delimposfwd-@delimposbwd-1)
end