Re: String Manipulation

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@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general