Checking for multiple substrings in an SQL query

Question

I have a column containing many rows. I am passing into a method a list of values. I wish to return all rows where a substring of this column contains the value I am looking for.

At the moment, I am using CHARINDEXto check for a single substring, and appending on OR CHARINDEX for every subsequent substring. It's quite messy, as I am sure you can appreciate.

So, at the moment, I have :

[Long SQL query]...
queryString.Append(string.Format(" (AND CHARINDEX( '{0}', Table.Column ) > 0 ", ListOfValues[0]));
            foreach (string value in ListOfValues)
            {
                queryString.Append(string.Format("OR CHARINDEX( '{0}', Table.Column ) > 0 ", value));
            }

            queryString.Append(string.Format(")AND CHARINDEX( '{0}', Table.Column) > 0)"));

queryString.Append(")");

Is there are less syntactically horrific way to do this ? :)

Thanks

What is your last Append meant to be doing? It seems to be calling Format with a placeholder but no parameters and I can't work out what it should be doing... — Chris
– Chris, Commented Jul 30, 2012 at 9:54
You should be able to update your post to correct the offending code. — Chris
– Chris, Commented Jul 30, 2012 at 13:54

Steve Homer · Accepted Answer · 2012-07-30 10:04:43Z

2

If I understand the question then you can do this on the SQL side more easily.

Select MyColumn
From MyTable
Where MyColumn Like '%MySubstringText%'

Obviously you would need to create the SQL statement dynamically if your sub string is going to change.

EDIT:

If you've multiple possible substrings then you could populate a table with the list of values and then cross join the two tables and filter using CharIndex.

Create table MySubstrings
(
    MySubstring nvarchar(100) not null
)


Select t.MyColumn
From MyTable t, MySubstrings s
Where CharIndex(s.MySubstring, t.MyColumn) > 0

The performance may not be great though.

edited Jul 30, 2012 at 10:04

answered Jul 30, 2012 at 9:50

Steve Homer

3,9422 gold badges26 silver badges43 bronze badges

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

2 Comments

Chris Over a year ago

Like is probably less syntactically horrible than charindex but you're still going to need the loop and the long set of this or this or this...

Steve Homer Over a year ago

I've edited the answer to deal with the multiple values possibility.

Steen Tøttrup · Accepted Answer · 2012-07-30 10:00:15Z

0

First off, putting string into a SQL query like that, is very risky! If you don't watch out, you're opening your application up for SQL injection http://en.wikipedia.org/wiki/SQL_injection.

And now to the answer:

queryString.Append(string.Format(" Table.Column LIKE '%{0}%' ", ListOfValues[0]));

To avoid SQL inject, try doing this instead:

command.Parameters.AddWithValue("@ParamX","%" + value + "%");

As we're doing string comparison here, I can't think of a less messy way of handling it with a SQL server.

edited Jul 30, 2012 at 10:00

answered Jul 30, 2012 at 9:52

Steen Tøttrup

3,8352 gold badges24 silver badges37 bronze badges

Collectives™ on Stack Overflow

Checking for multiple substrings in an SQL query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related