Compare items in a SQL column with array

Question

I have a SQL server table where I want to query for items on rows which have a value equal to an item in Array. Here's my example:

Column1    Column2    Column3
-------    -------    -------
aaa        100        200
bbb        100        400
aaa        200        78  
ccc        200        200

And my array:

string[] arr = {"ddd", "aaa", "fff", "bbb"}

So, I want to return from SQL rows matching items in the array. my SQL query should return items where column1 matches an item in the array:

Column1    Column2    Column3
-------    -------    -------
aaa        100        200
bbb        100        400
aaa        200        78

This all in C# by the way. Help will be appreciate.

Have you considered using LINQ? Perfect for this sort of multisource comparisons. — Sportball
– Sportball, Commented Mar 21, 2014 at 20:26

Juliano A. Felipe · Accepted Answer · 2014-03-21 20:20:06Z

3

You can build an SQL query with an IN(@PARAM) clause, and Join them with String.Join, like this:

using(SqlConnection connection = new SqlConnection(
        connectionString)) {
    using(SqlCommand command = new SqlCommand()) {
            SqlDataReader reader = null;
        try {
            command.Connection = connection;
            command.CommandText = "SELECT * FROM <TABLE> WHERE <FIELD> IN (@PARAM)";
            command.CommandType = CommandType.Text;
            command.Parameters.Add(new SqlParameter(@"@PARAM",
                    String.Join(",", <array>)));
            connection.Open();
            reader = command.ExecuteReader();
            if(reader.HasRows) {
                // logic
            }
        } catch(SqlException) {
            throw;
        }
    }
}

The items must be enclosed with ' ' (single quotes).

edited Mar 21, 2014 at 20:20

answered Mar 21, 2014 at 20:13

Juliano A. Felipe

5841 gold badge7 silver badges15 bronze badges

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

1 Comment

Munatela Over a year ago

Great answer, I guess I was getting stuck on how to use a parameter to pass the values in the array back to SQL. Thanks much for your help.

SoulTrain · Accepted Answer · 2014-03-21 20:14:25Z

0

You could pass these values as a Table Value Parameter to your SP and then join it to your table on Column1.

Link: How to pass a TVP from C#

answered Mar 21, 2014 at 20:14

SoulTrain

1,9041 gold badge12 silver badges11 bronze badges

Comments

Hamidreza · Accepted Answer · 2021-09-12 19:52:27Z

0

The SQL query you want is:

SELECT * FROM Table1 WHERE Column1 IN ('ddd', 'aaa', 'fff', 'bbb')

You can parse your array directly into the query.

Your post didn't mention anything about LINQ, which would make it even easier.

edited Sep 12, 2021 at 19:52

Hamidreza

7761 gold badge9 silver badges25 bronze badges

answered Mar 21, 2014 at 20:12

Hypersapien

6232 gold badges9 silver badges25 bronze badges

Collectives™ on Stack Overflow

Compare items in a SQL column with array

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related