CODEWITHSUNDEEP
sqlsql-serversearchsql-server-2012
Smithy
Smithy

Reputation: 2190

SQL Search Query with substring

I'm trying to convert some searching C# code over to an old SQL project but I don't know how to:

  1. split strings in SQL
  2. perform a "WHERE LIKE IN (@subTerms)"

Edit: I'm using MS SQL Server 2012.

Sorry I thought my question had enough information, the corresponding SQL table would look like:

TableName: Products

Columns: ID (int), Name (varchar)

Data:

MyProduct

My Stuff

Super Product

My SQL would look something like:

DECLARE @term varchar;
SET @term = "My Product"

DECLARE @subTerms varchar/array;
SET @subTerms = ???  (Split Term by ' ')

SELECT *
FROM Products
WHERE Name LIKE IN (@subTerms)

The term I have provided should pull all of the records available in the database.

Upvotes: 0

Views: 815

Answers (1)

StriplingWarrior
StriplingWarrior

Reputation: 156524

Hard to say exactly, based on what you've written, but it should be something like this:

SELECT * FROM Items WHERE Name in ('MyProduct', 'My Stuff', 'Super Product')

By the way, I've found it LINQPad to be very useful in situations like this: you can point it at a database, write a LINQ query, and then click the "SQL" tab to see what SQL was produced for that query. The SQL tends to be overly verbose, but you can usually get a pretty good idea of what it's doing and come up with a simplified version yourself.

Update

Based on the updated question, it's more clear what you want to do. I would recommend that you create a full-text search capable field, and use FREETEXT to query it. This is exactly the sort of thing that full-text search was made for.

Any text, including words, phrases or sentences, can be entered. Matches are generated if any term or the forms of any term is found in the full-text index.

Upvotes: 4

Related Questions