broad match search results, splitting the search by spaces

[Joseph Charles]

Hi there I have a simple search form on a website built in classic ASP, which im searching through a MySQL database table for a match with.

The database contains a lot of items labeled by size.

So for example if a user searches for: 38mm x 21mm
They will get a match because that is part of the name of the item in the database.

However the problem I have is that if they search for: 38 x 21
or for: 21mm x 38mm
Nothing is found.

I need to find a way to split the search up so all these searches work.
I would like to have it so that it searches for each part between the spaces seperately. I thought I found something on Google called FreeText which should do this, but there is very little info on it, and I cant get it to work.

Here are a few things ive tried so far:
rsCustSearch__testvar = Request.form("search")

rsCustSearch.Source = "SELECT * FROM Products WHERE ProductName LIKE '%" + Replace(rsCustSearch__testvar, "'", "''") + "%' OR LLength LIKE '%" + Replace(rsCustSearch__testvar, "'", "''") + "%' OR LHeight LIKE '%" + Replace(rsCustSearch__testvar, "'", "''") + "%'"

Doesnt match the way described above(38 x 21, or 21mm x 38mm), will only match if I search for 38 or 21 or I search for 38mm x 21mm

rsCustSearch.Source = "SELECT * FROM Products WHERE FREETEXT(*,"'%" + Replace(rsCustSearch__testvar, "'", "''") + "%'")"

Just gives me an error about syntax

rsCustSearch.Source = "SELECT * FROM Products WHERE Contains(*,'%" + Replace(rsCustSearch__testvar, "'", "''") + "%')"

Just gives me an error about syntax



Please can anyone help, im tearing my hair out...