Re: How to search ignoring spaces and minus signs

[Grzegorz JaÅkiewicz <gryzman@xxxxxxxxx>]

select regexp_replace(myval, E'(\\D)', '', 'g') from foo;

for added speed, you might consider this:
CREATE INDEX ON foo((regexp_replace(myval, E'(\\D)', '', 'g'))::bigint);


which is also going to protect you against inserts where value doesn't
contain any digits.

and added benefit of index:
gj=# select * from foo where (regexp_replace(myval::text,
'(\D)'::text, ''::text, 'g'::text)::bigint) = 5656;
 id | myval
----+-------
 61 | 56-56
(1 row)

Time: 1.356 ms
gj=# explain select * from foo where (regexp_replace(myval::text,
'(\D)'::text, ''::text, 'g'::text)::bigint) = 5656;
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Index Scan using foo_regexp_replace_idx on foo  (cost=0.01..8.38
rows=1 width=17)
   Index Cond: ((regexp_replace((myval)::text, '(\D)'::text, ''::text,
'g'::text))::bigint = 5656)
(2 rows)

gj=# \d+ foo
                                             Table "public.foo"
 Column |         Type          |                    Modifiers
            | Storage  | Description
--------+-----------------------+--------------------------------------------------+----------+-------------
 id     | integer               | not null default
nextval('foo_id_seq'::regclass) | plain    |
 myval  | character varying(20) | not null
            | extended |
Indexes:
    "foo_pkey" PRIMARY KEY, btree (id)
    "foo_regexp_replace_idx" btree ((regexp_replace(myval::text,
'(\D)'::text, ''::text, 'g'::text)::bigint))
Has OIDs: no


or even create a view that would allow you to make it nice and easy:
gj=# CREATE VIEW foo_view AS select id, (regexp_replace(myval::text,
'(\D)'::text, ''::text, 'g'::text)::bigint) AS int_val FROM foo;
CREATE VIEW

gj=# select * from foo_view where int_val = 1212;
 id | int_val
----+---------
 17 |    1212
(1 row)

Time: 0.709 ms
gj=# explain select * from foo_view where int_val = 1212;
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Index Scan using foo_regexp_replace_idx on foo  (cost=0.01..8.39
rows=1 width=17)
   Index Cond: ((regexp_replace((myval)::text, '(\D)'::text, ''::text,
'g'::text))::bigint = 1212)
(2 rows)


HTH

-- 
GJ

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general