July 1, 2009 at 2:46 pm
i.e.
say something like this..
select * from addressinfo
where
contains(address1,'"*red*"')
I'd expect it to find words that begin with red such as redding, redbird, redmond, etc... as well as just the word red.
But if I change the *red* to *edding* I would expect it to pick up redding still... but it doesn't
Is there a way to specify a partial match based on any part of the word that I'm overlooking?
July 1, 2009 at 3:37 pm
mtassin (7/1/2009)
i.e.say something like this..
select * from addressinfo
where
contains(address1,'"*red*"')
I'd expect it to find words that begin with red such as redding, redbird, redmond, etc... as well as just the word red.
But if I change the *red* to *edding* I would expect it to pick up redding still... but it doesn't
Is there a way to specify a partial match based on any part of the word that I'm overlooking?
select * from addressinfo
where address1 like '%red%'
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 1, 2009 at 3:44 pm
WayneS (7/1/2009)
mtassin (7/1/2009)
i.e.
select * from addressinfo
where address1 like '%red%'
Thanks Wayne... that's not what I meant.
I'm looking for a way to leverage FTI because using LIKE is forcing table scans and this looks like somethign it was designed for. Basically being google for text searches.
July 1, 2009 at 5:32 pm
have you tried the freetext function? that should return any word that contains the word 'red'
select * from addressinfo
where
freetext(address1,N'red')
Its not as precise as contains so you should compare results.
---------------------------------------------------------------------
July 8, 2009 at 1:22 pm
george sibbald (7/1/2009)
have you tried the freetext function? that should return any word that contains the word 'red'select * from addressinfo
where
freetext(address1,N'red')
Its not as precise as contains so you should compare results.
Doesn't work either... it misses Redding, Fred Street, Kitredge, etc...
July 8, 2009 at 1:22 pm
george sibbald (7/1/2009)
have you tried the freetext function? that should return any word that contains the word 'red'select * from addressinfo
where
freetext(address1,N'red')
Its not as precise as contains so you should compare results.
Doesn't work either... it misses Redding, Fred Street, Kitredge, etc...
July 9, 2009 at 11:51 am
Sorry, it is one of the numerous limitations in SQL Server's full-text functionality. Ref BOL: http://technet.microsoft.com/en-us/library/ms142492(SQL.90).aspx
Oracle, on the other hand, supports true SQL wildcarding (%_) of search terms. Ref: http://download.oracle.com/docs/cd/B14117_01/text.101/b10730/cqoper.htm#sthref1178
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply