Re: How to search ignoring spaces and minus signs

On 10/13/2010 07:45 PM, 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?
>

There are many options to do that...

You could use a regular expression like this:

DMP=# select * from foo where prod_code ~ '1[ -]*2[ -]*3[ -]*4[ -]*4';
prod_code
-----------
12 3-44
12-3-44
123 44

[ -]* means "zero or more dashes or spaces".

Maybe easier or (computationally) faster (YMMV on both counts) would be
to replace() the dashes and spaces on the fly first and only search the
cleaned string:

DMP=# select prod_code,
replace(replace(prod_code, '-', ''),' ','') from foo
where replace(replace(prod_code, '-', ''),' ','') = '12344';
prod_code | replace
-----------+---------
12 3-44 | 12344
12-3-44 | 12344
123 44 | 12344

Or just store the codes in a uniform format to begin with.

Christian