SQL Server : T-SQL中拆分字符串的一个自定义函数

今天写到的一个函数create function [dbo].[split](@SourceSql varchar(8000),@StrSeprate varchar(10))returns @temp table(F1 varchar(100))asbegindeclare @i intset @SourceSql = rtrim(ltrim(@SourceSql))set @i = charindex(@StrSeprate,@SourceSql)while @i >= 1beginif len(left(@SourceSql,@i-1))>0begininsert @temp values(left(@SourceSql,@i-1))endset @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)set @i=charindex(@StrSeprate,@SourceSql)endif @SourceSql <> ''insert @temp values(@SourceSql)returnend

使用实例

ALTER PROC [dbo].[GetTaskData](@userorgroup NVARCHAR(50))ASSELECT WorkflowNameUrl,FormId,WorkflowType,CreateBy,CreateTime,LasttimeInquisitor,LasttimeProcessTime,State,NextInquisitor From Table_Task WHERE EXISTS(SELECT F1 FROM split(NextInquisitorEn,';') WHERE F1=@userorgroup)