t-sql string concatenation

Question

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.

@CResults: "please see my answer below" What's the point of that comment? He'll already get a notification that you've answered, which someone with your rep clearly knows. So...?! — T.J. Crowder
– T.J. Crowder, Commented Mar 19, 2010 at 22:46
@stackoverflowuser - please consider editing your question to suit the accepted answer — CResults
– CResults, Commented Mar 19, 2010 at 23:06
@stackoverflowuser: Please, review my answer, you will change your mind :) — garik
– garik, Commented Mar 19, 2010 at 23:37

T.J. Crowder · Accepted Answer · 2010-03-19 22:28:40Z

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

answered Mar 19, 2010 at 22:28

T.J. Crowder

1.1m201 gold badges2k silver badges2k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

stackoverflowuser Over a year ago

i already tried that and it doesn't seem to be working. But the same approach works when dealing with integer data type

T.J. Crowder Over a year ago

@stackoverflowuser: I've used this with varchar columns plenty of times; it's not a column type issue. You want to be sure that null doesn't appear as a result of the subselect, is your where on the subselect ensuring that? The more info you provide, the better, really.

stackoverflowuser Over a year ago

you are right. i tried out on a sample table and that seems to work. But it does not work with my table. So here is the entire thing - i have a user defined function that returns a table ( ID int, TypeName varchar(8000)) i am doing this select * from TableA where Type in (select TypeName from dbo.udf_MyFunction(someinput)) But the above does not work.

stackoverflowuser Over a year ago

my bad. i had an extra space in front of each typename that was throwing off the results.

T.J. Crowder Over a year ago

@stackoverflowuser: Cool! glad you got that solved. Happy coding!

garik · Accepted Answer · 2010-03-19 23:35:46Z

1

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

edited Mar 19, 2010 at 23:35

answered Mar 19, 2010 at 23:27

garik

5,7665 gold badges33 silver badges44 bronze badges

Comments

CResults · Accepted Answer · 2010-03-19 22:40:17Z

0

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

edited Mar 19, 2010 at 22:40

answered Mar 19, 2010 at 22:28

CResults

5,1051 gold badge24 silver badges29 bronze badges

Collectives™ on Stack Overflow

t-sql string concatenation

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related