Tuesday, September 17, 2013

SQL Server function to seperate or split delimited single string column

CREATE FUNCTION [dbo].[awrfn_Split]
(
@String varchar(200),
@Delimiter char(1)
)  
RETURNS @TempTable TABLE (items varchar(200))  
as  
begin
 
declare @intPosition int,
@vchElement varchar(200)  
     
  if len(@String) < 1 or @String is null
  return  
 
  set @intPosition = 1
  while @intPosition <> 0  
  begin  
    set @intPosition = charindex(@Delimiter, @String)  
    if @intPosition <> 0  
      set @vchElement = left(@String, @intPosition - 1)  
    else  
      set @vchElement = @String  
   
    if len(@vchElement) > 0
      insert into @TempTable(items) values(@vchElement)  

    set @String = right(@String, len(@String) - @intPosition)  

    if len(@String) = 0 break  
  end

return
end

No comments:

Post a Comment