将一个分隔符字符串转换为一个表代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】将一个分隔符字符串转换为一个表

作者:松子 / 发布于2017/11/20/ 1003

将一个分隔符字符串转换为一个表; 如：a,b,c==>value ---------------- a b c

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SplitStr]') 
	AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[SplitStr]
GO

CREATE FUNCTION [dbo].[SplitStr] (@text varchar(max),@splitChar varchar(max))
RETURNS @splitTable TABLE 
(
    value varchar(max)
)
AS
BEGIN
	DECLARE @textLen INT	
	DECLARE @lp INT
	DECLARE @commaPos INT 
	DECLARE @remainText VARCHAR(MAX)	 
	DECLARE @splitText VARCHAR(MAX)
	--DECLARE @splitTable table
	--(
	--value varchar(max)
	--)
	--set @text='1,2,6,8,102,103,104,106,107,108,109,110,111,112,113,115,201,202,203,204,205,206,207,208,209,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,301,302,303,304,305,306,307,309,310'
	SET @remainText=@text
	SET @textLen=LEN(@text)
	SET @commaPos=1
	--set @splitChar=','

	WHILE(@commaPos!=0)
	BEGIN
		--PRINT 'enter while'
		--
		SET @commaPos=CHARINDEX(@splitChar,@remainText)
		--PRINT @commaPos
		IF(@commaPos is null Or @commaPos=0)
		BEGIN
			IF(LEN(@remainText)>0)
			BEGIN
				INSERT INTO @splitTable
				SELECT @remainText
			END
			BREAK 
		END
		--
		SET @splitText=LEFT(@remainText,@commaPos)
		--
		INSERT INTO @splitTable
		SELECT REPLACE(@splitText,@splitChar,'')
		--
		SET @remainText=RIGHT(@remainText,LEN(@remainText)-len(@splitText))
		
		--PRINT @remainText
	END	
   RETURN
END
GO

试试其它关键字

同语言下

可能有用的

松子贡献的其它代码(11)