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!