T-SQL 'Contains' predicate: question
-
I have an MS-SQL database with full-text indexing enabled on a table.
I know I can use the Contains predicate to search the table for values, ie:
Select * from Table1 where Contains(Column1, 'MyText')
.. I know I can use a wildcard to find all rows where a column's data starts with a particular combination of characters, ie:
Select * from Table1 where Contains(Column1, ' "MyText*" ')
Now here's my question: Is there a way to find all rows wheres a column's data ENDS with a particular combination of characters? the obvious solution (putting the wildcard at the start of the search criteria) doesn't work.
Has anyone encountered the same problem and found a solution?
-
I believe the answer is no... If my understanding of full-text indexing is correct, it indexes each word found in the indexed fields individually. Thus, the following sentence:
The quick brown fox jumps over the lazy dog.
Would be indexed like:
brown
dog
fox
jumps
lazy
over
quick
the (assuming "the" is not filtered out as a noise word)and as such, you could search for "do*" or "dog" and get the record, but a search for "*og" wouldn't.
The equivalent T-SQL operators supported by FT are exact matches (=), and starts with (MyText%), but not ends with (%MyText)... It simply doesn't know how to find text ending with a sequence of characters.
-
Thanks RaspenJho for your input. Seems we need to find another way. Cheers.
-
I'm by no means a sql expert, but couldn't you do something similar to:
select * from <table> where substring(<field>, strlen(<field>) - 4, 4) = <last 4 characters to search for>
-
I am assuming you want to avoid a table scan. The only workaround I see is to store the reverse of the string in another field and reference that.
Using the quick brown fox example, you would store "god yzal eht revo spmuj xof nworb kciuq ehT".
Then if you wanted to search for *og, you would use a contains query with "go*" as the argument.
If you are searching the middle of the string, you would have to use like and there is no way I can see to avoid a scan.
Crappy solution to a crappy problem, sorry.
-Whale
-
@whalemangler said:
Using the quick brown fox example, you would store "god yzal eht revo spmuj xof nworb kciuq ehT".
The main proble with this solution of course, is that you have to be very careful not to summon up Cthulhu.
-
If the table is used ONLY for at-the-end searches, then it's OK I think. But if there's a chance you'll have to do regular queries someday...
-
@Tweenk said:
If the table is used ONLY for at-the-end searches, then it's OK I think. But if there's a chance you'll have to do regular queries someday...
Are we still talking about Cthulhu?
-
@whalemangler said:
The only workaround I see is to store the reverse of the string in another field and reference that.
Using the quick brown fox example, you would store "god yzal eht revo spmuj xof nworb kciuq ehT".
Assuming you'd want to make full use of it, you'd also have to add loads of reversed word stems, noise words and whatever other things come as part of this. I'm farly sure there's a load of that kind of stuff that sits behind it.
