Technical Article

From a Delimited String, fetching the nth Value

,

This User defined Function will provide you the facility of fetching the nth Value from a Delimited string. The Parameter for the function which you have to pass is, the Delimited String, the Delimiter of the string, nth Position of the string. In this function , you can dynamically change the Delimiter as well as the position.
For an Example:
select dbo.StringToArray('sample1,sample2,sample3',',',2)
The above query will return the value of 'Sample2'.

CREATE Function dbo.StringToArray(@pstrValue Varchar(1000),@pstrDelim varchar(1),@pintPosition Int)
Returns Varchar(200)
As
Begin
Declare @strDelim varchar(1),
@Count Int,
@intPosition Int,
@strReturn varchar(200),
@intstartLocation Int
Select @strDelim = @pstrDelim,@Count = 1,@intPosition = 0,@intStartLocation = 1
While ( @Count >0 and @intPosition < @pintPosition and Len(@pstrValue) > @intPosition )
Begin
Select @Count = CharIndex(@strDelim,@pstrValue,@intStartLocation)

If @Count > 0  
Begin
Select @intPosition = @intPosition +1

Select @strReturn = Substring(@pstrValue,@intstartLocation  ,@Count -@intstartLocation)
Select @intstartLocation = @Count + 1
End
Else
Select @strReturn = Substring(@pstrValue,@intstartLocation,Len(@pstrValue))
End
Return @strReturn 
End

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating