Build where clause from arrays of strings using contains keyword in SQl Server

Question

I have a string like:

"bottle 750 green"

The string has a variable number of words each time. I need , based on this string to create:

CONTAINS(descr_en,'"*bottle*" and "*750*" and "*green*"')

I will then attach this to a where clause on an sql statement in order to get the desired results.

How can i create the resulting contains clause?

I have tried this:

var str = "mara 750 bottle";
    string[] strarr = str.Split(' ');
    var result = "";
    foreach (var s in strarr)
    {
        result += "\"*" + s + "*\"" + " AND ";
    }

This gives me : "*mara*" AND "*750*" AND "*bottle*" AND which is almost what i need.

The only thing i need from the above is to get rid of the training " AND "...

I prefer a clean solution based on LINQ if possible.

jezzarax · Accepted Answer · 2013-03-26 09:54:19Z

3

string.Join(" AND ", "mara 750 bottle".Split(' ').Select (s => string.Format("\"*{0}*\"", s)))

gives "*mara*" AND "*750*" AND "*bottle*"

answered Mar 26, 2013 at 9:54

jezzarax

4132 silver badges10 bronze badges

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

Comments

Daniel Imms · Accepted Answer · 2013-03-26 09:54:10Z

0

Here is a fairly clean method.

public string BuildContainsClause(string filter)
{
    var keywords = filter
        .Split(' ')
        .Select(e => string.Format("\"*{0}*\"", e));

    return string.Format("CONTAINS(descr_en,'{0}')", string.Join(" AND ", keywords));
}

Usage:

var contains = BuildContainsClause("bottle 750 green");
// contains = "CONTAINS(descr_en,'\"*bottle*\" AND \"*750*\" AND \"*green*\"')"

answered Mar 26, 2013 at 9:54

Daniel Imms

50.6k19 gold badges157 silver badges170 bronze badges

Comments

Izzy · Accepted Answer · 2013-03-26 09:55:37Z

0

/*Why not just build your CONTAINS clause based on:

//This is not literally suitable.
foreach( string s in samplestring.Split(' '))
{
    yourstringbuilder.append(" and *");
    yourstringbuilder.append(s);
    yourstringbuilder.append("*");
}

?*/

EDIT: For your edited question - just result = result.remove(result.Length - " AND ".Length); after your loop

edited Mar 26, 2013 at 9:55

answered Mar 26, 2013 at 9:48

Izzy

1,8461 gold badge19 silver badges34 bronze badges

1 Comment

e4rthdog Over a year ago

I updated my question as i think i am doing what you say BUT i need to get rid of the ending AND...

Collectives™ on Stack Overflow

Build where clause from arrays of strings using contains keyword in SQl Server

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related