Thread: rtrim giving weird result
I'm running Postgres 7.0.3 on a RedHat Linux 6.1. For some reason, rtrim is giving me an incorrect result: db01=# SELECT tablename FROM pg_tables WHERE tablename LIKE '%_opto' AND tablename NOT LIKE 'pg%' ORDER BY tablename ASC ; tablename -----------------center_out_optocircles_optoellipse_optoex_ellipse_optofigure8_optoro_ellipse_opto (6 rows) Now I want to return the same thing only with the trailing '_opto' removed: db01=# SELECT rtrim(tablename, '_opto') FROM pg_tables WHERE tablename LIKE '%_opto' AND tablename NOT LIKE 'pg%' ORDER BY tablename ASC ; rtrim ------------center_ou <======================= NOTE: the trailing 't' is missingcirclesellipseex_ellipsefigure8ro_ellipse (6 rows) However, as you can see, the 'center_out' table is missing the last 't'. If I exclude the '_': db01=# SELECT rtrim(tablename, 'opto') FROM pg_tables WHERE tablename LIKE '%_opto' AND tablename NOT LIKE 'pg%' ORDER BY tablename ASC ; rtrim -------------center_out_ <======================= 't' shows up againcircles_ellipse_ex_ellipse_figure8_ro_ellipse_ (6 rows) The 't' is back. Is there something that I'm doing wrong with my query here? Thanks. -Tony
"G. Anthony Reina" <reina@nsi.edu> writes:
> I'm running Postgres 7.0.3 on a RedHat Linux 6.1. For some reason, rtrim
> is giving me an incorrect result:
No, you have an incorrect understanding of rtrim. The second argument
is a set of removable characters, not a string to be matched.
AFAIK we are following Oracle in defining it that way ...
regards, tom lane
Hi, all, Could somebody tell me if there is a work around to create "union on view" (which seems not implemented in the postgres yet) ? Also, is there any alternative query that can do: select * from (select * from table); I could not find an answer from the old archieve, and sorry if this has been answered previously. (I am new here :) Regards, Jae
If you're willing to wait or use the betas, 7.1 should probably do both of these. (Won't quite make toast though). [Although I believe the second'll be something like: select * from (select * from table) alias;] On Wed, 14 Mar 2001, Jae-Woong Hwnag wrote: > Hi, all, > > Could somebody tell me if there is a work around to > create "union on view" (which seems not implemented > in the postgres yet) ? > > Also, is there any alternative query that can do: > > select * from (select * from table); > > I could not find an answer from the old archieve, > and sorry if this has been answered previously. > (I am new here :)
The second parameter to "rtrim" is interpreted as a set of characters and
rtrim:
"Returns string with final characters removed after the last character not
in set"
So rtrim("center_out_opto", "_opto") returns "center_ou"
because "u" is not in the set {o, p, t, _} but all the characters after it
are.
rtrim("center_out_opto", "pot_") will produce the same thing.
----- Original Message -----
From: "G. Anthony Reina" <reina@nsi.edu>
To: "pgsql-hackers@postgreSQL.org" <pgsql-hackers@postgresql.org>
Sent: Wednesday, March 14, 2001 9:14 PM
Subject: [HACKERS] rtrim giving weird result
> I'm running Postgres 7.0.3 on a RedHat Linux 6.1. For some reason, rtrim
> is giving me an incorrect result:
>
> db01=# SELECT tablename FROM pg_tables WHERE tablename LIKE '%_opto' AND
>
> tablename NOT LIKE 'pg%' ORDER BY tablename ASC ;
> tablename
> -----------------
> center_out_opto
> circles_opto
> ellipse_opto
> ex_ellipse_opto
> figure8_opto
> ro_ellipse_opto
> (6 rows)
>
> Now I want to return the same thing only with the trailing '_opto'
> removed:
>
>
> db01=# SELECT rtrim(tablename, '_opto') FROM pg_tables WHERE tablename
> LIKE '%_opto' AND tablename NOT LIKE 'pg%' ORDER BY tablename ASC ;
> rtrim
> ------------
> center_ou <=======================
> NOTE: the trailing 't' is missing
> circles
> ellipse
> ex_ellipse
> figure8
> ro_ellipse
> (6 rows)
>
> However, as you can see, the 'center_out' table is missing the last 't'.
> If I exclude the '_':
>
> db01=# SELECT rtrim(tablename, 'opto') FROM pg_tables WHERE tablename
> LIKE '%_opto' AND tablename NOT LIKE 'pg%' ORDER BY tablename ASC ;
> rtrim
> -------------
> center_out_
> <======================= 't' shows up again
> circles_
> ellipse_
> ex_ellipse_
> figure8_
> ro_ellipse_
> (6 rows)
>
> The 't' is back.
>
> Is there something that I'm doing wrong with my query here?
>
> Thanks.
> -Tony
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
Ken Hirsch wrote:
> So rtrim("center_out_opto", "_opto") returns
> "center_ou"
> because "u" is not in the set {o, p, t, _} but all the characters after it
> are.
> rtrim("center_out_opto", "pot_") will produce the same thing.
>
That seems like an odd definition (although as Tom points out, it is
consistent with Oracle).
Is there a way to just remove the "_opto" from the end of the string?
-Tony
On Thu, Mar 15, 2001 at 09:34:04AM -0800, G. Anthony Reina wrote:
> Ken Hirsch wrote:
>
> > So rtrim("center_out_opto", "_opto") returns
> > "center_ou"
> > because "u" is not in the set {o, p, t, _} but all the characters after it
> > are.
> > rtrim("center_out_opto", "pot_") will produce the same thing.
> >
Modulo the correct quoting conventions for strings, of course.
>
> That seems like an odd definition (although as Tom points out, it is
> consistent with Oracle).
Yup, I got bit by it, trying to remove 'The ' from the front of a set of
words, in order to get an approximation of 'library sort'.
>
> Is there a way to just remove the "_opto" from the end of the string?
If you have exactly one known string to (optionally) remove, this works
(and even works if the string is missing. Watch out for the early
occurance of substring problem, though!):
test=# select substr('center_out_opto',1,(strpos('center_out_opto','_opto')-1)); substr
------------center_out
(1 row)
test=# select substr('center_out_opto',1,(strpos('center_out_opto','foo')-1)); substr
-----------------center_out_opto
(1 row)
test=#
Ross
"Ross J. Reedstrom" <reedstrm@rice.edu> writes:
>> Is there a way to just remove the "_opto" from the end of the string?
> If you have exactly one known string to (optionally) remove, this works
> (and even works if the string is missing. Watch out for the early
> occurance of substring problem, though!):
> test=# select substr('center_out_opto',1,(strpos('center_out_opto','_opto')-1));
My first thought for any moderately complicated string-bashing problem
is to write a function in pltcl or plperl ... they are much stronger in
string manipulation than SQL itself is.
regards, tom lane
On Thu, Mar 15, 2001 at 01:18:57PM -0500, Tom Lane wrote:
> "Ross J. Reedstrom" <reedstrm@rice.edu> writes:
> >> Is there a way to just remove the "_opto" from the end of the string?
>
> > If you have exactly one known string to (optionally) remove, this works
> > (and even works if the string is missing. Watch out for the early
> > occurance of substring problem, though!):
>
> > test=# select substr('center_out_opto',1,(strpos('center_out_opto','_opto')-1));
>
> My first thought for any moderately complicated string-bashing problem
> is to write a function in pltcl or plperl ... they are much stronger in
> string manipulation than SQL itself is.
Agreed, hence the caveats about 'exactly one string, that you know ahead of
time, and never appears as a substring ...'
But it _can_ be done, it's just not pretty. And it _is_ standard SQL:
here's the SQL92 spelling of the above:
SELECT SUBSTRING ('center_out_opto' FROM 1 FOR (POSITION ('_opto' IN 'center_out_opto') - 1));
Ross