CODEWITHSUNDEEP
c#sql-serverlinq
e4rthdog
e4rthdog

Reputation: 5223

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

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.

Upvotes: 2

Views: 789

Answers (3)

Izzy
Izzy

Reputation: 1816

/*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

Upvotes: 0

jezzarax
jezzarax

Reputation: 413

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

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

Upvotes: 3

Daniel Imms
Daniel Imms

Reputation: 50149

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*\"')"

Upvotes: 0

Related Questions