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
