Sql server 2005 CTE 拆分字符串

最新推荐文章于 2018-10-10 15:59:44 发布

soarheaven

最新推荐文章于 2018-10-10 15:59:44 发布

阅读量723

点赞数

分类专栏： SQL 文章标签： sql server string list table insert function

本文链接：https://blog.csdn.net/soarheaven/article/details/6109907

版权

SQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

alter FUNCTION [dbo].[UF_SpliteIntToTable]
(
     @String VARCHAR(8000)
)
RETURNS @Table TABLE (Id INT)
AS
BEGIN
     DECLARE @SpliteChar AS CHAR(1) = ',';

     WITH TempTable AS
     (
         SELECT CAST(SUBSTRING(@String,1,CHARINDEX(@SpliteChar,@String,1) - 1) AS INT) AS item,
         SUBSTRING(@String,CHARINDEX(@SpliteChar,@String,1) + 1, LEN(@String)) + @SpliteChar AS list

         UNION ALL

         SELECT CAST(SUBSTRING(list,1,CHARINDEX(@SpliteChar,list,1) - 1) AS INT) AS item,
         SUBSTRING(list,CHARINDEX(@SpliteChar,list,1) + 1, LEN(list)) AS list
         FROM TempTable
         WHERE LEN(TempTable.list) > 0
     )

     INSERT INTO @Table SELECT item FROM TempTable WHERE item > 0

     RETURN
END