Translate

> > sql server string split

sql server string split

Posted on Sunday, October 7, 2012 | No Comments

Use a table valued function like this,

CREATE FUNCTION Splitfn(@String varchar(8000), @Delimiter char(1))     
returns @temptable TABLE (items varchar(8000))       as       begin     
    declare @idx int     
    declare @slice varchar(8000)     

    select @idx = 1     
        if len(@String)<1 or @String is null  return     

    while @idx!= 0     
    begin     
        set @idx = charindex(@Delimiter,@String)     
        if @idx!=0     
            set @slice = left(@String,@idx - 1)     
        else     
            set @slice = @String     

        if(len(@slice)>0)
            insert into @temptable(Items) values(@slice)     

        set @String = right(@String,len(@String) - @idx)     
        if len(@String) = 0 break     
    end   return    
end

and get your variable and use this function like this,

 SELECT i.items FROM dbo.Splitfn(@a,'|') AS i


===============================================================================
Optional
use this function to generate a relation data with other table



create function getTeacherDepartmentSplit()
returns @tempTeacher table (TeacherID nvarchar(50),DepartmentID nvarchar(50))
as
begin

 DECLARE @TeacherID nvarchar(50)
 DECLARE @DepartmentIDs nvarchar(50)

 declare dbCursor cursor for
 SELECT     dbo.Teacher.TeacherID, dbo.Teacher.DepartmentIDs FROM  dbo.Teacher
 open dbCursor
 FETCH NEXT FROM dbCursor INTO @TeacherID,@DepartmentIDs

 While @@FETCH_STATUS = 0
 begin
  insert into @tempTeacher (TeacherID,DepartmentID)
  SELECT @TeacherID,i.items FROM dbo.Splitfn(@DepartmentIDs,',') AS i
 
  FETCH NEXT FROM dbCursor INTO @TeacherID,@DepartmentIDs
 
 end
 close dbCursor
 return
end

 select T.TeacherID,T.DepartmentID from getTeacherDepartmentSplit() as T 

Leave a Reply

Powered by Blogger.