t-sql string concatenation

问题:

i have a table that has following column

Type
--------
type 1
type 2
type 3

How can i convert the above to a string like ('type 1', 'type 2', 'type 3')

I want to use the output in my t-sql query with IN clause. Something like select * from TableA where SomeColumn IN ('Type 1','Type 2', Type 3')

I used to following to come up with output (type 1, type 2, type 3)

select '(' + STUFF((select ', ' + Type from TableA for xml path ('')),1,2,'') + ')'

But dont know how to insert the single quotes.

回答1:

The usual way is with a subselect:

select * from TableA where SomeColumn IN (
    select Type from TheOtherTable
)

I'm guessing you'd have a where clause on the subselect as well.

Depending on complexity, sometimes you do this with outer joins instead:

select * from TableA a
left outer join TheOtherTable b on a.SomeColumn = b.Type
where b.Type is not null

Which you use depends on the criteria you're applying to both the records from TableA and what I've called TheOtherTable (the one with Type).

回答2:

Just try it for fun :)

declare @s1 varchar(8000)
declare @s2 varchar(8000)

update t
  set  
    @s1 = ISNULL(@s1 + ',', '') + '''' + REPLACE(t.Type, '''', '''''') + ''''
   ,@s2 = 'select * from TableA where Type IN (' + @s1 + ')' 
from TableA t

select @s2

REPLACE(t.Type, '''', '''''') - if field has any apostrophe(s) in field's text, REPLACE will double it. Try to change type1 to typ'e1 or typ''e1 in your table TableA

I stopped joking...

Try to avoid IN clause and subqueries. They work very slow (table scan and so on...)! Use this:

select a.Type 
from TableA a 
  inner join TheOtherTable b 
  on a.Type = b.Type

回答3:

Whenever you need a quote, double type it

so ' becomes ''

so your code becomes

select '(' + STUFF((select ''',''' + Type from TableA for xml path ('')),1,2,'') + ''')'

The above generates

('type 1','type 2','type 3')