Help with query

[Guest]

I have the following record in my database:

Field = Name
Value = a test

When i run the following query using a form variable that is "atest" I don't get any results, but if i use a form variable that is "a test" then it works fine. What can i do in order to get that record when i input the search term "atest".

<cfquery name="test" datasource="#DSN#">
SELECT *
FROM tbl_main
where Name LIKE '%#form.name#%'
</cfquery>

[Newsgroup_User]

use you db's string manipulation functions to remove all whitespace from
the column's value?

but i would rather consider emplying some fulltext searcing, either
using your db's built-in functionality or using verity or similar tool,
if you need to return records that do not exactly match the entered
search term...

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Guest]

Thank you for your response, how do i enable verity on a database? I have been looking on google and can't find any information relating to that.

[wmanu]

I think what Azadi refering to is Verity Search Collection in ColdFusion.

http://tutorial121.easycfm.com/

http://livedocs.adobe.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=ColdFu...

[Guest]

Thank you for your response, after reading a bunch of stuff on verity and full text searching, i think full text searching is what i need. But now i can't figure out what i am doing wrong with this query, it doesn't get any results.

I have the following table with

tbl_company
===============================
cid(int) | 1 | 2 | 3 |
name(text) | a test | another test | delta company |
url(varchar) | www.atest.com | www.anothertest.com | www.deltacompany.com |

SELECT *
FROM tbl_company
WHERE MATCH (name) AGAINST ('a')

I am using mysql 5 on debian linux and have enabled fulltext on the name field

Can someone please help me with this? Any help is very much appreciated.