search in database- do you have other than "like" ? [modified]

[Abdul Rhman Alsri]

When we search with for "mobile" word, using "like" as follows:

select ID from Table where column like '%mobile%'

we can get: mobile, bile. But we can not get mobility or mobilization in result, even though they are related words for mobile word we are searching for. So, Do you have solutions to handle those related words, I mean when search for mobile, we can get in result the words mobility and mobilization?

modified on Thursday, May 6, 2010 3:07 PM

[Chris Meech]

Abdul-Rhman Alsri wrote:

related words for mobile word

Someone needs to define that relationship. One way is to include that in your query

select ID from Table where column like '% mobile %'
or column like '% mobilization %'
or column like '% mobility %'
;

:)

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

[Abdul Rhman Alsri]

I need it in general, because the word I search for is not determined explicity, but it's also retrieved from database.

SELECT a.id AS [CampaignID],
b.id AS [ZoneID],
a.adheadline,
a.adtext,
a.displayurl,
a.targeturl
FROM adcampaign a
INNER JOIN adzone b
ON a.sitecategory = b.category
AND a.LANGUAGE = b.LANGUAGE
AND a.keyword LIKE '%' + b.keyword + '%';

[Chris Meech]

Perhaps another table that links similar words is needed. :)

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

[T M Gray]

What database product are you using? If you are using MS SQL you can use things like SOUNDEX and FREETEXT.

[Abdul Rhman Alsri]

SELECT id FROM AdCampaign WHERE FREETEXT(Keyword, 'going');

Error: Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'AdCampaign' because it is not full-text indexed.