Re: How to search ignoring spaces and minus signs

Andrus wrote:
> CHAR(20) columns in 8.4 database may contains spaces and - signs like
>
> 13-333-333
> 12 3-44
> 33 33 333
> 12345
>
> User enters code to search without spaces and - signs, like 12344
> How to search for product code ignoring spaces and - signs?
>
> For example searching for code 12344 should return
> 12 3-44 as matching item.

Is your search an exact match ignoring the spaces and dashes, or a substring
search? If the field contained 0123445 then is a search for 12344 supposed to
match it or not?

The best simple way to do what you want is to add another CHAR(20) column for
each of the existing ones like you describe where the extra column has a copy of
the original one but with the spaces and dashes removed.

Then when doing searches you search on the new copy and when displaying you
display the original copy.

Doing this would save the database having to do the most expensive kinds of
computations repeatedly at the time of searching given that these can be
staticly precomputed.

Moreover, if your search is exact-match, you get additional speed gains by
having an index on the search column. (I don't know if there is any kind of
useful index for substring matches besides full text search.)

-- Darren Duncan