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
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
Show quoted text
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
"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:
Show quoted text
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 :)
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