Click here to Skip to main content
11,488,877 members (78,094 online)
Click here to Skip to main content

How to Get Words Separated in a String Concatenated by Delimiter

, 14 Apr 2014 CPOL 7.3K 4
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
Software Developer Maxus Technologies
India India
No Biography provided

Comments and Discussions

 
SuggestionEasy way to split string Pin
Member 107271924-Jul-14 1:45
memberMember 107271924-Jul-14 1:45 
GeneralThoughts Pin
PIEBALDconsult14-Apr-14 19:37
memberPIEBALDconsult14-Apr-14 19:37 
GeneralRe: Thoughts Pin
Imtiyaz_mi20-May-14 20:48
memberImtiyaz_mi20-May-14 20:48 
GeneralRe: Thoughts Pin
PIEBALDconsult21-May-14 4:05
memberPIEBALDconsult21-May-14 4:05 
QuestionA few points Pin
Member 1045413814-Apr-14 5:38
memberMember 1045413814-Apr-14 5:38 
AnswerRe: A few points Pin
Ankur .K. Vishwakarma14-Apr-14 7:07
memberAnkur .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
Web03 | 2.8.150520.1 | Last Updated 14 Apr 2014
Article Copyright 2014 by Ankur .K. Vishwakarma
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid