Re: String Manipulation - Mailing list pgsql-general

From Sam Mason
Subject Re: String Manipulation
Date
Msg-id 20090612220537.GA5407@samason.me.uk
Whole thread Raw
In response to String Manipulation  (Christine Penner <christine@ingenioussoftware.com>)
Responses Re: String Manipulation
List pgsql-general
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/

pgsql-general by date:

Previous
From: Christine Penner
Date:
Subject: String Manipulation
Next
From: Christine Penner
Date:
Subject: Re: String Manipulation