Click here to Skip to main content
Click here to Skip to main content

How to Get Words Separated in a String Concatenated by Delimiter

, 14 Apr 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
This tip shows you a T/SQL function to split a string concatenated by a 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 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ankur .K. Vishwakarma
Student TIMSCDR
India India
No Biography provided

Comments and Discussions

 
SuggestionEasy way to split string PinmemberMember 107271924-Jul-14 1:45 
GeneralThoughts PinmemberPIEBALDconsult14-Apr-14 19:37 
GeneralRe: Thoughts PinmemberImtiyaz_mi20-May-14 20:48 
GeneralRe: Thoughts PinmemberPIEBALDconsult21-May-14 4:05 
QuestionA few points PinmemberMember 1045413814-Apr-14 5:38 
AnswerRe: A few points PinmemberAnkur .K. Vishwakarma14-Apr-14 7:07 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141216.1 | Last Updated 14 Apr 2014
Article Copyright 2014 by Ankur .K. Vishwakarma
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid