Em 28/07/2012 13:46, Adrian Klaver escreveu:
On 07/27/2012 04:58 PM, Edson Richter wrote:
I've a select with the following expression:
select trim(both ' ' from substring(rslinha2 from 5 for position('(-)'
in rslinha2)-6))
from ...
problem is that the spaces are not being removed from either side.
What
would be wrong?
FYI the '' is redundant, empty spaces are removed by default.
Some examples of what is in rslinha2 before the above and what you are
seeing after would help.
Thanks for your help,
--
*Edson Carlos Ericksson Richter*
/SimKorp Informática Ltda/
Fone: (51) 3366-7964
Celular: (51)9318-9766/(51) 8585-0796
Embedded Image
Yes, I know the redundancy. Nevertheless, it did not work.
The information about the data follows:
rslinha2 character varying
content for rslinha2 (this is one of several, but they are similar -
data has been imported into this field from file using foreign table
with file_fdw):
"2. TAXA VIGILANCIA (+) R$ 13,00"
resulting substring expression is:
" TAXA VIGILANCIA "
(1 space at beginning, and several after)
If I do apply trim over the substring, no spaces are removed, and I
cannot understand why.
Complete (not working) expression are:
-------------------------------------------------------------------------------------------------------------------------
select trim(both ' ' from substring(rslinha2 from 5 for position('(+)'
in rslinha2)-6))
from ...
-------------------------------------------------------------------------------------------------------------------------
or
-------------------------------------------------------------------------------------------------------------------------
select trim(substring(rslinha2 from 5 for position('(+)' in
rslinha2)-6))
from ...
-------------------------------------------------------------------------------------------------------------------------
But using the regular expression matching "^\s*" and "\s*$" works, and
spaces are removed:
-------------------------------------------------------------------------------------------------------------------------
select regexp_replace(regexp_replace(substring(rslinha2 from 5 for
position('(+)' in rslinha2)-6)), '^\s*', ''), '\s*$', '');
-------------------------------------------------------------------------------------------------------------------------
Maybe I've hit a bug in Postgres, or just I could not fully understand
the usage for trim (I admit, I was expecting trim to behave like in MS
SQL or Java).
