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
