SQL Server几个简单函数

------------恢复内容开始------------

1. 表值函数：f_SplitStr

CREATE FUNCTION [dbo].[f_SplitStr]
(
    @str NVARCHAR(MAX),
    @split NVARCHAR(50)='/'   
)
RETURNS @table table(COL NVARCHAR(50))
AS
BEGIN
    DECLARE @s NVARCHAR(MAX)
    DECLARE @tmpS NVARCHAR(500)
    DECLARE @i INT
    SET @i=1
    SET @s=@str+@split
    WHILE(CHARINDEX(@split,@s)<>0)
    BEGIN
        SET @tmpS=LTRIM(RTRIM(SUBSTRING(@s,1,CHARINDEX(@split,@s)-1)))
        IF LEN(@tmpS)>0
        BEGIN
            INSERT INTO @table VALUES(@tmpS)
        END
        SET @s=STUFF(@s,1,CHARINDEX(@split,@s),'')
    END
    RETURN
END 

 

 

2.表值函数：f_Split

CREATE FUNCTION f_Split
(
    @chr nvarchar(max),
    @split nvarchar(2)
)
returns @table TABLE(COL NVARCHAR(1000))
AS
BEGIN
    WHILE(CHARINDEX(@split,@chr)<>0)
    BEGIN
        INSERT INTO @TABLE(COL) VALUES(SUBSTRING(@chr,1,CHARINDEX(@split,@chr)-1))
        SET @chr=STUFF(@chr,1,CHARINDEX(@split,@chr),'')
    END
    INSERT INTO @table VALUES(@chr)
    RETURN
END