如何在SQL Server中为字符串中的每个字符添加逗号和单引号?

I have this:

我有这个:

declare @data varchar = 'UI'

I need to fix this so it'll work with a "IN" statement.

我需要修改它，这样它就可以使用IN语句了。

'UI' => 'U','I'

Having a hard time getting this done. Anyone care to help?

很难做到。有人愿意帮忙吗?

Thanks!

谢谢!

3 个解决方案

#1

You can use an ad-hoc tally table in concert with Stuff() and XML.

您可以使用一个与Stuff()和XML一致的临时计数表。

Example

例子

declare @data varchar(max) = 'UI'

Select S = Stuff((Select ',' +S
    From (
            Select S=''''+substring(@data,N,1)+''''
             From (Select Top (Len(@data)) N=Row_Number() Over (Order By (Select null)) From master..spt_values ) N
        ) B1
    For XML Path ('')),1,1,'')

Returns

'U','I'

Edit If you are open to a UDF

编辑如果你对UDF开放。

Select S = Stuff((Select ',''' +RetVal + ''''
 From (Select * From [dbo].[udf-Str-Parse-Char](@data)) A 
 For XML Path ('')),1,1,'')

The UDF

UDF

CREATE FUNCTION [dbo].[udf-Str-Parse-Char] (@String varchar(max))
Returns Table
As
Return (
    with   cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
           cte2(N) As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From cte1 a,cte1 b,cte1 c,cte1 d,cte1 e,cte1 f)

    Select RetSeq=N
          ,RetVal=Substring(@String,N,1) 
     From  cte2
) 
--Max 1 Million Observations
--Select * from [dbo].[udf-Str-Parse-Char]('this is a string')

#2

Simple way:

简单的方法:

declare @data varchar = 'UI'
select '''' + @data + ''''

to use this in IN Statement:

在声明中使用这句话:

DECLARE @this_Command varchar(1000)
set @this_Command = 'select * from yourtable where column1 in(''' + @data + ''')'
exec(@this_Command);

#3

The answer from @JohnCappelletti will serve you well, but unless I'm missing something, I cant help but feel there are easier approaches available.

@JohnCappelletti的答案会对你很有帮助，但除非我漏掉了什么，否则我不得不觉得有更简单的方法。

declare @data varchar = 'UI'

Select * from t
where charindex(field,@data)>0

This where clause will evaluate to true if field matches any of the characters in the @data parameter. There is an edge case which could cause issues: if field wasn't always a single character then charindex could give you a multi-character match which would have been avoided by @JohnCappelletti's answer. Should that be a relevant concern, you could do this:

如果字段匹配@data参数中的任何字符，则where子句将计算为true。有一种可能导致问题的边缘情况:如果字段不总是单个字符，那么charindex可以为您提供一个多字符匹配，而@JohnCappelletti的答案可以避免这种匹配。如果这是一个相关的问题，你可以这样做:

Select * from t
where charindex(field,@data)>0 AND
len(field)=1

If you know @data won't contain any 'special' characters (such as ] or ^), then you can use like and would do it as follows:

如果你知道@ data不会包含任何“特殊”字符(比如]或^),那么您可以使用像和做如下:

Select * from t
where field like '[' + @data + ']'

The like will evaluate to true if field matches any of the characters in the @data parameter. If you wanted to do it this way and you do (or could) have special characters in there, you'd need to escape those characters first.

如果字段匹配@data参数中的任何字符，like将计算为true。如果您想这样做，并且您确实(或者可能)在其中有特殊字符，您需要先转义这些字符。

Given that, I'd normally opt for the charindex approach. like also tends to be less efficient than other functions, though others will know better than I whether it is in this case.

考虑到这一点，我通常会选择charindex方法。与其他功能相比，它的效率也要低一些，不过，在这种情况下，其他人会比我更清楚。

#1