Re: How to search ignoring spaces and minus signs

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Andrus <kobruleht2(at)hot(dot)ee>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: How to search ignoring spaces and minus signs
Date: 2010-10-13 20:58:54
Message-ID: AANLkTikgxoNwy7wfPEVrLu0VG6fb0f2GQmoXYhZ4s02Y@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Darren Duncan 2010-10-13 21:01:16 Re: How to search ignoring spaces and minus signs
Previous Message Pavel Stehule 2010-10-13 20:53:04 Re: How to search ignoring spaces and minus signs