Friday, July 5, 2013

SPLIT FUNCTION Comma Seperator with RowId



CREATE FUNCTION [dbo].[fn_Split](@locationlist varchar(5000))                   
RETURNS @ParsedList Table (Rowid int,location Varchar(100))                   
AS                   
BEGIN   
 Set @locationlist = @locationlist + ','   
 DECLARE @location varchar(100), @Pos int                   
 declare @cnt int               
 SET @location =replace((LTRIM(RTRIM(@locationlist))),'''','')           
 SET @Pos = CHARINDEX(',', @locationlist, 1)                     
 Set @cnt = 1                     
 IF REPLACE(@locationlist, ',', '') <> ''                     
 BEGIN                     
  WHILE @Pos > 0                     
  BEGIN                     
   SET @location = LTRIM(RTRIM(LEFT(@locationlist, @Pos - 1)))                     
   IF @location  <> ''                     
   BEGIN                     
    INSERT INTO @ParsedList (Rowid,location)                      
    VALUES (@cnt,@location) --Use Appropriate conversion                
 Set @cnt = @cnt + 1                    
   END                     
   SET @locationlist = RIGHT(@locationlist, LEN(@locationlist) - @Pos)                     
   SET @Pos = CHARINDEX(',', @locationlist, 1)                     
                     
  END                     
 END                      
 RETURN                     
END

No comments:

Post a Comment