将字符串转换成TABLE返回函数

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER FUNCTION [dbo].[str2tempTable] (@p_string     VARCHAR (3000),
                                   @p_sep        VARCHAR (20) = ',')
   RETURNS   @tmp     TABLE   (tempstr   VARCHAR(50))
AS
   BEGIN
   DECLARE @beginSearchPos   INT

      DECLARE @nextSearchPos   INT

      DECLARE @length   INT

      DECLARE @tempSqlStr VARCHAR(3000)
      SET @beginSearchPos = 1
      SET @nextSearchpos = 0
      SET @length = len (@p_string)


      WHILE @beginSearchPos <> 0 AND @nextSearchPos < @length
         BEGIN
            SET @nextSearchPos =
                   charindex (@p_sep, @p_string, @beginSearchPos)

            IF @nextSearchPos = 0
               BEGIN
                  INSERT @tmp SELECT substring (@p_string,
                                   @beginSearchPos,
                                   @length - @beginSearchPos + 1)

                  SET @beginSearchPos = 0
               END
            ELSE
               BEGIN

               INSERT @tmp SELECT substring (@p_string,
                                   @beginSearchPos,
                                   @nextSearchPos - @beginSearchPos)
                  SET @beginSearchPos = @nextSearchPos + 1
               END
         END
      RETURN
   END

实例：

select * from dbo.str2tempTable('123', ',')；

第一个参数为传进去的字符串，第二个参数为分隔符！