Thread: String Manipulation
Hi, 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. Any suggestions would be appreciated Christine Penner Ingenious Software 250-352-9495 christine@ingenioussoftware.com
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/
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
On Fri, Jun 12, 2009 at 03:35:44PM -0700, Christine Penner wrote:
> 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?
The first thing is to define what you want it to do; pick some values
and define what the output should be and go from there. If you've got
say, "A123", "#125a" and "12-7" and you want them in that order then I'd
strip out any non-numeric digits, convert it to a number and then sort
on that. regexp_replace is your friend here.
> 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
What do you get back for simple things like:
SELECT substring('1a','^[0-9]+');
I'd expect you to get '1' back out. If you're not getting this out then
you'll need to say which version of PG you're using as functions like
this get added with each major version. Most useful docs for you are
in:
http://www.postgresql.org/docs/current/static/functions-string.html
You can get to older releases quickly by replacing "current" with things
like "8.2" and "7.4".
--
Sam http://samason.me.uk/
Sam,
I get nothing. I just updated recently but the only version number I
can find is 8.3. I know its at least 8.3.4 but should be more.
Christine
At 03:58 PM 12/06/2009, you wrote:
>On Fri, Jun 12, 2009 at 03:35:44PM -0700, Christine Penner wrote:
> > 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?
>
>The first thing is to define what you want it to do; pick some values
>and define what the output should be and go from there. If you've got
>say, "A123", "#125a" and "12-7" and you want them in that order then I'd
>strip out any non-numeric digits, convert it to a number and then sort
>on that. regexp_replace is your friend here.
>
> > 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
>
>What do you get back for simple things like:
>
> SELECT substring('1a','^[0-9]+');
>
>I'd expect you to get '1' back out. If you're not getting this out then
>you'll need to say which version of PG you're using as functions like
>this get added with each major version. Most useful docs for you are
>in:
>
> http://www.postgresql.org/docs/current/static/functions-string.html
>
>You can get to older releases quickly by replacing "current" with things
>like "8.2" and "7.4".
>
>--
> 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
On Fri, Jun 12, 2009 at 04:07:11PM -0700, Christine Penner wrote:
> I get nothing. I just updated recently but the only version number I
> can find is 8.3. I know its at least 8.3.4 but should be more.
OK, the main thing is that you're running a copy of PG from the 8.3
series. I've just tried it on a reasonably recent 8.3.7 and an old
8.3.0 I have and I get what I'd expect back (i.e. a string containing
the number '1').
I'd start to question things like are you talking to the database you
think you are, which client are you using and other details like that.
If you can connect through psql it should tell you the server version
and if you could paste a complete session that would help. For example,
I get:
sam@willow:~$ psql
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
sam=> SELECT substring('1a','^[0-9]+');
substring
-----------
1
(1 row)
sam=>
--
Sam http://samason.me.uk/
On Jun 13, 2009, at 12:35 AM, Christine Penner wrote:
> 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.
The above regular expression assumes values start with a number, so it
won't return anything useful for values like 'A123' or '#123a' and
will just return '23' for '23-233'. I don't think Sam intended it to
be used with the values in your database but just to illustrate how a
regular expression could be used.
I think what you want is something like:
select regex_replace(b_lot_or_st_no, '[^0-9]', '', 'g')
This globally replaces everything that's not a number by '',
effectively removing it from the text.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4a33833c759151518024860!
Alban,
That was exactly what I was looking for.
Thanks
Christine
At 03:45 AM 13/06/2009, you wrote:
>On Jun 13, 2009, at 12:35 AM, Christine Penner wrote:
>
>>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.
>
>
>The above regular expression assumes values start with a number, so it
>won't return anything useful for values like 'A123' or '#123a' and
>will just return '23' for '23-233'. I don't think Sam intended it to
>be used with the values in your database but just to illustrate how a
>regular expression could be used.
>
>I think what you want is something like:
> select regex_replace(b_lot_or_st_no, '[^0-9]', '', 'g')
>
>This globally replaces everything that's not a number by '',
>effectively removing it from the text.
>
>Alban Hertroys
>
>--
>If you can't see the forest for the trees,
>cut the trees and you'll see there is no forest.
>
>
>!DSPAM:737,4a33833c759151518024860!
>
>
>
>--
>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general