函数代码如下
CREATEFUNCTION[dbo].[fnSplitStr](
@sTextNVARCHAR(Max),
@sDelimCHAR(1)
)
RETURNS@retArrayTABLE(
valueVARCHAR(100)
)
AS
BEGIN
DECLARE
@posStartBIGINT,
@posNextBIGINT,
@valLenBIGINT,
@sValueNVARCHAR(100);
IF@sDelimISNULL
BEGIN
IFLEN(@sText)>100SET@sText=SUBSTRING(@sText,1,100)
INSERT@retArray(value)
VALUES(@sText);
END
ELSE
BEGIN
SET@posStart=1;
WHILE@posStart<=LEN(@sText)
BEGIN
SET@posNext=CHARINDEX(@sDelim,@sText,@posStart);
IF@posNext<=0
SET@valLen=LEN(@sText)-@posStart+1;
ELSE
SET@valLen=@posNext-@posStart;
SET@sValue=SUBSTRING(@sText,@posStart,@valLen);
SET@posStart=@posStart+@valLen+1;
IFLEN(@sValue)>0
BEGIN
IFLEN(@sValue)>100SET@sValue=SUBSTRING(@sValue,1,100)
INSERT@retArray(value)
VALUES(@sValue);
END
END
END
RETURN
END
|