CODEWITHSUNDEEP
sqlsql-servert-sqlfull-text-search
user1439618
user1439618

Reputation: 235

SQL: transform full-text search into like construction

I've got stored procedure that performs search using full-text indexes in general case. But I can't build full-text index for one field, and I need to use LIKE construction.

So, the problem is: parameter could be

"a*" or "b*"

like parameter for CONTAINS command.

Сan anyone give a good solution, how to transform this parameter for LIKE construction.

Thank you.

P.S: I use MSSQL Server

Upvotes: 1

Views: 237

Answers (3)

user1439618
user1439618

Reputation: 235

Thanks to everyone!

Unfortunately expression parsing is not enough for general case.

I use regular expressions in MS SQL SERVER

http://anastasiosyal.com/POST/2008/07/05/REGULAR-EXPRESSIONS-IN-MS-SQL-SERVER-USING-CLR.ASPX

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

In the end, you will probably need to use dynamic SQL.

Here is a way you can get the correct WHERE clause, given that input:

declare @str varchar(255) = '"a*" or "b*"';

with const as (select 'col' as col)
select col+' like '+replace(replace(REPLACE(@str, '"', ''''), '*', '%'), 'or ', 'or '+COL+' like ') as WhereClause
from const

The "const" is just a table with one column to specify your column name. It allows it to be specified in one place.

This just does replaces to get the correct syntax for LIKE. Of course, this would be more complex to support more functionality from CONTAINS.

Upvotes: 0

WolfgangGroiss
WolfgangGroiss

Reputation: 189

Depending on the full-text search constructs you want to support, this is generally impossible.

According to MSDN, full-text search syntax on SQL Server supports these constructs:

One or more specific words or phrases (simple term)

something along LIKE '%[,;.-()!? ]Term[,;.-()!? ]%'

A word or a phrase where the words begin with specified text (prefix term)

something along LIKE '%[,;.-()!? ]Term%'

Inflectional forms of a specific word (generation term)

Not possible

A word or phrase close to another word or phrase (proximity term)

Not possible

Synonymous forms of a specific word (thesaurus)

Not possible

Words or phrases using weighted values (weighted term)

Not possible

Those which I have marked "not possible" can't really be translated to LIKE queries, but of course you could get inventive (using your own stemming algorithm for inflectional forms, or your own thesaurus for synonyms) to support at least some of those.

Upvotes: 2

Related Questions