如何在SQL中将逗号分隔的数据转换为差异格式 [英] how to convert comma seperated data to diff format in sql

问题描述

在sql server 2008中，表的列中的数据为1,2,3，其数据类型为varchar(MAx)
我想将其转换为"1"，"2"，"3"
如何在SQL中实现
我尝试但给我错误，因为将数据类型varchar转换为数值时出错.

I have data in column of table as 1,2,3 whose data type is varchar(MAx) in sql server 2008
i want to convert it into ''1'',''2'',''3''
how it possible in SQL
i try but it gives me error as Error converting data type varchar to numeric.

推荐答案

那实际上并不简单-SQL没有数组的概念！ /> 但是，您可以将其转换为临时表，然后使用它.这是一个存储过程，它接受用逗号分隔的值的列表，并将其用作查询的IN参数-几乎就是您要执行的操作.

Thats actually not simple - SQL does not have a concept of an array!
However, you can convert it into a temporary table, and use that. This is a stored procedure which accepts a list of commas separated values and uses that as the IN parameter of a query - pretty much what you are trying to do.

Pass a list as a string parameter:
DECLARE @INSTR as VARCHAR(MAX)
SET @INSTR = '2,3,177,'
DECLARE @SEPERATOR as VARCHAR(1)
DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
SET @SEPERATOR = ','
CREATE TABLE #tempTab (id int not null)
WHILE PATINDEX('%' + @SEPERATOR + '%', @INSTR ) <> 0
BEGIN
   SELECT  @SP = PATINDEX('%' + @SEPERATOR + '%',@INSTR)
   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
   INSERT INTO #tempTab (id) VALUES (@VALUE)
END
SELECT * FROM myTable WHERE id IN (SELECT id FROM #tempTab)
DROP TABLE #tempTab

我发现了有趣的文章:
拆分功能 [ ^ ]
将varchar拆分为单词[ 使用字符串分割字符串的简单而有效的方法Transact-SQL [

I have found interesting articles:
Split function[^]
Split a varchar into words[^]
An Easy But Effective Way to Split a String using Transact-SQL[^]

这篇关于如何在SQL中将逗号分隔的数据转换为差异格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！