字符串分割函数

USE [SBCRM]
GO
/****** Object:  UserDefinedFunction [dbo].[f_StringSplit]    Script Date: 10/19/2012 12:14:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[f_StringSplit](@str nvarchar(max),@spliter nvarchar(10))
returns @tb table(ch nvarchar(256))
AS
BEGIN
--declare @str nvarchar(max)
--declare @spliter nvarchar(10)
--set @str = 'Lougp,Zhengy'
--set @spliter = ','
DECLARE @Num int,@Pos int, @NextPos int
SET @Num = 0
SET @Pos = 1
WHILE(@Pos <= LEN(@str))
BEGIN
SELECT @NextPos = CHARINDEX(@spliter, @str, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@str) + 1
INSERT INTO @tb VALUES(LOWER(RTRIM(LTRIM(SUBSTRING(@str, @Pos, @NextPos - @Pos)))))
SELECT @Pos = @NextPos+1
END
return
END