/*******************************************************************************
Description             Delimit a string and return specified segment
Author:                     Peter Yates
Example usage:        exec delimiter 'te~st~in~g1~23', '~', 5
Modifications:
*******************************************************************************/
 
IF EXISTS  (SELECT * 
            FROM dbo.sysobjects 
            WHERE id = object_id(N'[dbo].[delimiter]') 
            AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP procedure [dbo].[delimiter]
go
 
CREATE procedure delimiter(
 @str nvarchar (4000), --delimited string
 @del nvarchar (10), --delimiter
 @sect int --section of string wanted
 )
AS
begin
 declare @nextstr nvarchar(4000)
 declare @pos int
 declare @nextpos int
 CREATE TABLE #valuetable (id int identity, value varchar(500))
 SET @nextstr = ''
 SET @str = @str + @del
 SET @pos = charindex(@del,@str)
 SET @nextpos = 1
 while (@pos <>  0)  
 begin
  SET @nextstr = substring(@str,1,@pos - 1)
     INSERT INTO #valuetable 
      ([value]) 
      VALUES 
      (@nextstr)
  SET @str = substring(@str,@pos +1,len(@str))
  SET @nextpos = @pos
  SET @pos  = charindex(@del,@str)
 end
   SELECT value 
   FROM #valuetable
   WHERE id = @sect
 RETURN
end