Building SQL "where in" statement from list of strings in one line?

Question

I have a List(Of String) which corresponds to "types" on our database table.

We are using the DB2 ADO.NET provider and my final query needs to look something like this:

select * from table where type in (@type1, @type2, @type3, @type4)

In the past, I've built the list of query parameters / host variables using a ForEach loop, but I would really like to figure out a way to build them in one line. Of course, I can join all of the strings, but adding the "@" and the incrementing digit is giving me headaches.

Anyone have any ideas on how to do this?

in .net 4 there is the String.Concat method but getting the index of the item in the iEnumerable is a pain and not a one liner. stackoverflow.com/questions/1290603/… I'd be interested in the solution for this. — Mike Miller
– Mike Miller, Commented Jun 7, 2011 at 22:21

StriplingWarrior · Accepted Answer · 2011-06-08 03:41:56Z

15

Won't something like this work?

var inList = "(" + string.Join(", ", typeList.Select(t => "@" + t)) + ")";

Edit

Based on your comment, how about this?

var inList = "(" + 
    string.Join(", ", Enumerable.Range(1, argCount).Select(i +> "@type" + i)) +
    ")";

edited Jun 8, 2011 at 3:41

answered Jun 7, 2011 at 22:31

StriplingWarrior

158k29 gold badges261 silver badges326 bronze badges

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

3 Comments

Brian Dishaw Over a year ago

I've have had a similar issue in the past and building these up by hand in a pain. I really like the elegence of the linq method!

Dan J Over a year ago

Good ol' LINQ-to-String to the rescue. :)

TaylorOtwell Over a year ago

Well, I actually need the parameters to literally be @type1, @type2, etc. With the numbers and all. I'm just trying to create a parameter for each type and I'll fill each parameter with the actual values of the types before executing the query.

Jimmy · Accepted Answer · 2021-03-24 05:12:25Z

5

This is how I generally do this

string.Join(",", items.Select(i => $"'{i}'");

answered Mar 24, 2021 at 5:12

Jimmy

5875 silver badges10 bronze badges

Comments

Akhil · Accepted Answer · 2011-06-07 22:38:44Z

2

string dbCommand = 
    string.Format("select * from table where type in ({0})", string.Join(",", typeList.Select(p => "@" + p));

answered Jun 7, 2011 at 22:38

Akhil

7,6201 gold badge26 silver badges23 bronze badges

Comments

Maxali · Accepted Answer · 2011-06-08 15:19:06Z

0

Split the list using as string.Join(",", listType.ToArray())

    string types = string.Join(",", listType.ToArray());

    Select * from table where type in (types)

answered Jun 8, 2011 at 15:19

Maxali

1,9922 gold badges16 silver badges25 bronze badges

Comments

F Blanchet · Accepted Answer · 2019-05-29 15:59:38Z

0

A simple way could be to use :

"'" + string.Join("','", myListOfNames) + "'")

edited May 29, 2019 at 15:59

F Blanchet

1,5283 gold badges23 silver badges35 bronze badges

answered May 29, 2019 at 15:13

elhumidio

112 bronze badges

Comments

Richard Schneider · Accepted Answer · 2011-06-07 22:19:25Z

-1

SQL/ADO.NET does not support arrays. So you really have to build the SQL by hand.

SQL 2008 does support a Table parameter, but it seems a lot of overhead. See http://www.sommarskog.se/arrays-in-sql-2008.html for more details.

answered Jun 7, 2011 at 22:19

Richard Schneider

35.5k9 gold badges59 silver badges74 bronze badges

Collectives™ on Stack Overflow

Building SQL "where in" statement from list of strings in one line?

6 Answers 6

Edit

3 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Edit

3 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related