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

查看:90
本文介绍了如何在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屋!

查看全文
相关文章
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆