如何在表格中获取逗号分隔的字符串值 [英] How to get comma separated string value in a table

问题描述

你好，
我想将字符串值放入表中.该字符串包含逗号分隔的值


我写了这个查询，但是它不能正常工作.
最后一个值未正确获取

Hi there
I want to get the string values into a table.The string contains comma separated values


I wrote this query but it doesn''t work fine.
The last value is not getting properly

DECLARE	@AnswerId		AS	VARCHAR(50)
DECLARE	@AnswerId1		AS	VARCHAR(50)
DECLARE	@Length			AS	VARCHAR(50)
DECLARE	@LengthToTrim	AS	VARCHAR(50)

CREATE	TABLE	#AnswerTable(ansId	Int)
SELECT	@AnswerId	=	'2342,2342,234'

IF	(CHARINDEX(',',@AnswerId)	=	0)
BEGIN		
	INSERT	INTO	#AnswerTable
	VALUES			(@AnswerId)
END
ELSE
BEGIN
	WHILE	(@AnswerId	!=	'')
	BEGIN
		SET		@AnswerId1		=	SUBSTRING(@AnswerId,0,CHARINDEX(',',@AnswerId))
		SELECT	@LengthToTrim	=	LEN(@AnswerId1)
		SELECT	@AnswerId		=	RIGHT(@AnswerId,LEN(@AnswerId)-(@LengthToTrim + 1))
 
   			INSERT	INTO	#AnswerTable
			VALUES			(@AnswerId1)
		
		IF	@AnswerId	=	''	 
 			BREAK
		ELSE
  			CONTINUE
	END
END
SELECT	ansId
FROM	#AnswerTable

DROP		
TABLE	#AnswerTable

在此先感谢

Thanks in advance

推荐答案

这是什么?

what about this?

DECLARE @InputString NVARCHAR(MAX)
DECLARE @Delimiter NVARCHAR(40)
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @NextString NVARCHAR(40)

SET @InputString = ''hello,its,a,beautiful,day''
SET @Delimiter = '',''
SET @Pos = charindex(@Delimiter,@InputString)

SET @InputString = @InputString + @Delimiter

CREATE TABLE #Results(Tokens NVARCHAR(MAX))

WHILE (@pos <> 0)
BEGIN
	SET @NextString = substring(@InputString,1,@Pos - 1)
		
	INSERT INTO #Results VALUES (@NextString)
	
	SET @InputString = substring(@InputString,@pos+1,len(@InputString))
	SET @pos = charindex(@Delimiter,@InputString)
END

SELECT * FROM #Results

DROP TABLE #Results

这篇关于如何在表格中获取逗号分隔的字符串值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！