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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
