[TSQL] Freetexttable / Containstable

[Dick Montage]

I am attempting to implement a full text search in T-SQL. I have a table (cds_Stdnxx) that contains two fields (ProdID, Description).

I would like to search the description field using either FREETEXTTABLE or CONTAINSTABLE. However, an issue with the descriptions:

Two meters is expressed as "2 m", not "2m". This is always the case for any measurement related descriptions.

Now, using CONTAINSTABLE I can search for "black NEAR patch NEAR cable" which is fine. But a user is most likely going to enter "2m" rather than "2 m".

Searching for "black NEAR patch NEAR cable NEAR 2m" is fine and valid, but obviously gives no results.

Searching for "black NEAR patch NEAR cable NEAR 2 NEAR m" is not valid as 'A clause of the query contained only ignored words'.

Searching for "black NEAR patch NEAR cable NEAR '2 m'" works perfectly, but no user will enter: black patch cable '2 m'.

Can anyone suggest another way?

Thanks guys

[Dick Montage]

Anyone here know this? I know it could end up quite complex.

[Glowstick]

Write a scanner tool that looks for stuff like that and wraps it into apostrophes before constructing the query? Using regexes or whatever.

[Dick Montage]

Holy cow, I totally neglected to think about RegEx's. There's gonna be quite a few caveats to this, as I bet idiot users will also enter part numbers (which do not contain spaces) but hey.

Worth a try, cheers!