how to add commas and single quotes for each character in a string in SQL Server?

Question

I have this:

declare @data varchar = 'UI'

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

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

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

Thanks!

Answer 1

You can use an ad-hoc tally table in concert with Stuff() and 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

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

The 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') 

Answer 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);

Answer 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.

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:

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:

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.

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.