CODEWITHSUNDEEP
c#sqlsql-serverlinq
skub
skub

Reputation: 2306

SQL search between commas

I am working on a requirement to search for words that appear only between two commas within a string.

So for example, in my database, I have the following data:

------------------------------------------------------------------------
| ID  | TITLES
------------------------------------------------------------------------
|  1  | THE QUICK BROWN FOX , JUMPS OVER , THE LAZY DOG
------------------------------------------------------------------------
|  2  | THE, QUICK FOX JUMPS NIGHTLY , ABOVE WIZARD
------------------------------------------------------------------------

So when people search QUICK FOX they should get both rows.

However when they search QUICK FOX JUMPS, it should only return row 2, since those words only appear in the same section of commas. It should not return the first row. The words QUICK, FOX and JUMPS are all in the first row, but they dont belong in the same section (separated by a comma).

Is it possible to create an SQL query for this? Currenly I am breaking up each comma and doing the search individualy within C# code (not very elegant though).

I hope this makes sense. Any help would be greatly appreciated.

Upvotes: 0

Views: 210

Answers (4)

Ben Reich
Ben Reich

Reputation: 16324

If you're willing to do this in C# (which sounds like an option), I think you can do it quite elegantly. Suppose myRows is IEnumerable<Row> for a type Row that represents a row, and mySearchTerms is an IEnumerable<string> that contains all the search terms.

myRows.Where(row => row.Title.Split(",")
                    .Any(part => mySearchTerms
                         .All(searchTerm => part.Contains(searchTerm)));

In other words, we are looking for rows where the string in between two commas contains all of the search terms.

Upvotes: 2

Veer Shrivastav
Veer Shrivastav

Reputation: 5496

This will return your desired result for "Quick Fox Jumps".

select ID, Titles
from Test
where Titles like '%Quick Fox Jumps%'

Now if you wish to have result for "Quick Fox", then try this:

select ID, Titles
from Test
where Titles like '%Quick Fox%'

% stands for any characters (before/after as per placement).

_ stands for only one character before/after as per placement.

So as many as number of _ you place that many characters are taken.

For more details read here

EDIT

If you want..

Row to return Quick and Jump, even if they are not together then search as:

%Quick%Jump%

Upvotes: 0

Andrey Shchekin
Andrey Shchekin

Reputation: 21609

If processing speed is important, the right solution is to use Full-Text Search:
Either standard SQL Server one, or an external indexer (Lucene etc).

When using an external indexer, you can control what you feed to it, so you can strip out things that are not between two commas.

If processing speed is unimportant, C# seems to be the best solution.

Upvotes: 0

gunr2171
gunr2171

Reputation: 17579

If you don't care about keeping the search term combined, you can use LIKE to get the full search term.

select ID, Titles
from theTable
where Titles like '%Quick Fox Jumps%'

Upvotes: 0

Related Questions