sql server 字符串分割成列表

/*
功能:分割字符
示例:select * from split('aa,bb,cc,dd',',')
*/
CREATE FUNCTION [dbo].[split] (
    @String VARCHAR(MAX),--字符串
    @Delimiter VARCHAR(MAX)--需分割的字符
) 
RETURNS @temptable TABLE (items VARCHAR(MAX)) AS BEGIN DECLARE @idx INT=1 DECLARE @slice VARCHAR(MAX) IF LEN(@String) < 1 OR LEN(ISNULL(@String,'')) = 0 RETURN WHILE @idx != 0 BEGIN SET @idx = CHARINDEX(@Delimiter,@String) IF @idx != 0 SET @slice = LEFT(@String,@idx - 1) ELSE SET @slice = @String IF LEN(@slice) > 0 INSERT INTO @temptable(items) VALUES(@slice) SET @String = RIGHT (@String, LEN(@String) - @idx) IF LEN(@String) = 0 BREAK END RETURN END

 

posted @ 2022-09-07 09:38  ~且听风吟~  阅读(441)  评论(0编辑  收藏  举报