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/