SQL 資料表值函式操作

標籤：style   blog   color   資料   io   re   

 

CREATE FUNCTION [dbo].[f_getpy_tb] ( @str VARCHAR(100) )RETURNS @tb TABLE ( re VARCHAR(8000) )AS     BEGIN        DECLARE @re TABLE ( id INT, re VARCHAR(8000) )  --資料處理中間表        DECLARE @i INT ,            @ilen INT ,            @splitchr VARCHAR(1)        SELECT  @splitchr = ‘ ‘ --兩個拼音之間的分隔字元(目的是為了通用性考慮)                ,                @i = 1 ,                @ilen = LEN(@str)        INSERT  INTO @re                SELECT  @i ,                        py                FROM    YingShe                WHERE   chr = SUBSTRING(@str, @i, 1)        WHILE @i < @ilen             BEGIN                SET @i = @i + 1                INSERT  INTO @re                        SELECT  @i ,                                re + @splitchr + py                        FROM    @re a ,                                YingShe b                        WHERE   a.id = @i - 1                                AND b.chr = SUBSTRING(@str, @i, 1)            END        INSERT  INTO @tb                SELECT  re                FROM    @re                WHERE   id = @i        RETURN     ENDGO