Re: How to search ignoring spaces and minus signs

2010/10/13 Andrus <kobruleht2(at)hot(dot)ee>:
> 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.

postgres=# select regexp_replace('1243-56 6536 7', E'[^0-9]', '', 'g');
regexp_replace
----------------
12435665367
(1 row)

that's the key. obviously, searching with that as predicate through
large table will be unpleasant unless you precalculate the above into
index expression.

merlin