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.
Christine
At 03:05 PM 12/06/2009, you wrote:
>On Fri, Jun 12, 2009 at 12:36:27PM -0700, Christine Penner wrote:
> > I want to calculate a new field I added to a table but I'm not sure
> > how to do it. This will be a copy of another field with any non
> > numeric characters stripped off the end and padded with spaces.
> >
> > This is what I was trying to do
> >
> > Update Buildings SET B_LOT_SORT=lpad(substr(lot,1,??),7)
> >
> > instead of ?? I need to know the position of the last numeric character.
>
>I'd personally use a regular expression, much easier:
>
> UPDATE buildings SET b_log_sort = lpad(substring(lot,'^[0-9]+'),7);
>
>If this is so that you can sort things based on their numeric order, why
>not just convert it to an integer rather than doing any padding?
>
>--
> Sam http://samason.me.uk/
>
>--
>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general