Alban, That was exactly what I was looking for. Thanks Christine At 03:45 AM 13/06/2009, you wrote:
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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
-- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
