Postfix term in SQL Server full text search
According to MSDN article , TSQL function 'CONTAINS' has an argument called prefix_term, the docs say:
<prefix_term>
Specifies a match of words or phrases beginning with the specified
text. Bind the prefix term in double quotation marks ("") and add an asterisk (*) before the end of the quotation mark so that all text starting with the simple term specified before the asterisk matches.
Well, this prefix, if I search for "Ira", it finds "Iran, Iran, Iraq, Invincible, etc."
But I'm looking for a postfix term, so if I search for "* ran" then I expect to see results in Iran, Trimaran, etc.
How can i do this?
[UPDATE]
I have a term like this in my ArticleContent column:
f5_Iran_three_islands
This request catches it:
Select Code,ArticleContent from Articles Where ArticleContent Like '%Ira%'
But I cannot catch this line from this request:
Select Code FROM Articles WHERE CONTAINS(ArticleContent, '"*Ira*"')
[/ UPDATE]
source to share
declare @value varchar(50), @value_contains varchar(53), @value_contains_reverse varchar(53)
set @value = '20602019';
set @value_contains = '"'+@value+'*"';
set @value_contains_reverse = '"'+reverse(@value)+'*"';
select charindex(@value, matchstring) from Table
where contains(matchstring, @value_contains) or contains(matchstring_reverse,@value_contains_reverse)
source to share
As you found, the docs indicate that only prefix terms are possible. MSDN: CONTAINS (Transact-SQL)
If you want to use postfix terms, you can either use standard wildcard searches (LIKE, etc.) or consider writing a .Net stored procedure that uses regular expression searches. I've done this before and writing sp wasn't that hard, although uploading it to the server was a pain (.Net 2, SQL Server 2005).
source to share