Search Postgresql Archives

Re: String Manipulation

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux