On Jun 13, 2009, at 12:35 AM, Christine Penner wrote:
Sam,
The problem with making it a numeric field is that I have seen
things like A123, #123a or 23-233. This is only here to make most
sorting work better, not perfect. It all depends on how they enter
the data. Wont the different formats make it harder to convert to a
number?
I tried your suggestion and haven't had any luck. For a quick test I
did this:
select b_lot_or_st_no, substring('1a','^[0-9]+') as TEST from
F_BUILDINGS
With this I tried using b_lot_or_st_no instead of 1a, I also
replaced the , with for like they do in the manual. I looked through
the manual but I'm still stuck.
The above regular expression assumes values start with a number, so it
won't return anything useful for values like 'A123' or '#123a' and
will just return '23' for '23-233'. I don't think Sam intended it to
be used with the values in your database but just to illustrate how a
regular expression could be used.
I think what you want is something like:
select regex_replace(b_lot_or_st_no, '[^0-9]', '', 'g')
This globally replaces everything that's not a number by '',
effectively removing it from the text.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4a33833c759151518024860!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
