日期:2014-05-17 浏览次数:20719 次
--相关函数
CREATE FUNCTION [dbo].[fn_SplitStr]
(
@String NVARCHAR(4000), --待分隔的字符串
@Separator NVARCHAR(4) --分隔符号
)
RETURNS @strTable TABLE (strVal NVARCHAR(4000)) --返回字符串列表
AS
BEGIN
DECLARE
@Occurrences INT,
@Counter INT,
@tmpStr NVARCHAR(4000)
SET @Counter = 0
IF SUBSTRING(@String,LEN(@String),1) <> @Separator
SET @String = @String + @Separator
SET @Occurrences = (DATALENGTH(REPLACE(@String,@Separator,@Separator+'#')) - DATALENGTH(@String))/ DATALENGTH(@Separator)
SET @tmpStr = @String
WHILE @Counter <= @Occurrences
BEGIN
SET @Counter = @Counter + 1
IF(LEN(SUBSTRING(@tmpStr,1,CHARINDEX(@Separator,@tmpStr)-1)) > 0)
INSERT INTO @strTable VALUES(SUBSTRING(@tmpStr,1,CHARINDEX(@Separator,@tmpStr)-1))
SET @tmpStr = SUBSTRING(@tmpStr,CHARINDEX(@Separator,@tmpStr)+1,4000)
IF DATALENGTH(@tmpStr) = 0
BREAK
E