如何在sql中添加逗号之前和之后的单个quatation [英] How to add a single quatation before and after comma in sql
本文介绍了如何在sql中添加逗号之前和之后的单个quatation的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
hi
i我在sql表中有一个列,其值包含为002,00DL,005S(varchar数据类型)
i希望输出为''002',' 00DL','005S''查看IN条款
ex select * from Employee,其中ID在()
提前感谢
解决方案
你可以使用下面的脚本
DECLARE @ Temp NVARCHAR ( 100 )
SELECT @ Temp = COALESCE ( @ Temp + ' ,',' ')+ [ColumnName]
FROM 表
不要在你的字符串中添加任何单个报价。
只需运行此拆分功能
CREATE FUNCTION [dbo]。[Split]
(
@ List nvarchar ( 2000 ),
@ SplitOn nvarchar ( 5 )
)
RETURNS @ RtnValue table
(
Id int identity ( 1 , 1 ),
Value nvarchar ( 100 )
)
AS
BEGIN
while (Charindex( @ SplitOn , @ List )> 0)
开始
插入 进入 @ RtnValue (值)
< span class =code-keyword>选择值= ltrim(rtrim(子串( @ List , 1 ,Charindex( @ SplitOn , @ List ) - 1)))
设置 @ List =子串( @ List ,Charindex( @ SplitOn , @ List )+ len( @SplitOn ),len( @ List ))
结束
插入 进入 @ RtnValue (值)
选择值= ltrim(rtrim( @ List ))
返回
END
和您的查询
声明 @ name nvarchar (max); //声明
选择 @ name = column 来自 table 分配值 变量
选择 * 来自员工其中 ID (选择值来自 dbo.Split( @ name ,' ,'))
< blockquote>
声明 @ output varchar (max)
set @ output = ' '''
选择 @ output = @ output + ' ''' + cast(col as varchar )+ ' '',' 来自 吨能够 其中 col 不 null
选择 LEFT ( @ output ,LEN( @ output ) - 1)+ ' '''
hi
i am having column in sql table which contain value as 002,00DL,005S(varchar data Type)
i want the output as ''002','00DL','005S'' to check IN Clause
ex select * from Employee where ID in ()
thanks in advance
解决方案
You can use below script
DECLARE @Temp NVARCHAR(100) SELECT @Temp = COALESCE(@Temp +',','')+ [ColumnName] FROM Table
Don't add any Single Quotation To your String.
Just run this Split Function
CREATE FUNCTION [dbo].[Split] ( @List nvarchar(2000), @SplitOn nvarchar(5) ) RETURNS @RtnValue table ( Id int identity(1,1), Value nvarchar(100) ) AS BEGIN While (Charindex(@SplitOn,@List)>0) Begin Insert Into @RtnValue (value) Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List)) End Insert Into @RtnValue (Value) Select Value = ltrim(rtrim(@List)) Return END
and your Query
declare @name nvarchar(max);// Declaration select @name=column from table Assign values to Variable select * from Employee where ID in (select Value from dbo.Split(@name,','))
declare @output varchar(max) set @output = '''' select @output = @output + '''' + cast(col as varchar) + ''',' from table where col is not null select LEFT(@output,LEN(@output)-1) + ''''
这篇关于如何在sql中添加逗号之前和之后的单个quatation的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文