CODEWITHSUNDEEP
sqlsql-server-2005t-sqlsql-server-2008
stackoverflowuser
stackoverflowuser

Reputation: 22390

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.

Upvotes: 0

Views: 902

Answers (3)

garik
garik

Reputation: 5756

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

Upvotes: 1

CResults
CResults

Reputation: 5105

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

Upvotes: 0

T.J. Crowder
T.J. Crowder

Reputation: 1075635

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

Upvotes: 1

Related Questions