Обсуждение: Trim not working (PostgreSQL 9.1.2 on Win64)

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?

Thanks for your help,

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
>
>


--
Adrian Klaver
adrian.klaver@gmail.com

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).


Edson.

Edson Richter <edsonrichter@hotmail.com> writes:
> If I do apply trim over the substring, no spaces are removed, and I
> cannot understand why.
> ...
> But using the regular expression matching "^\s*" and "\s*$" works, and
> spaces are removed:

I think what this means is that what you say are runs of spaces are no
such thing, but are some other whitespace character(s).  Perhaps tabs,
or non-breaking spaces?

            regards, tom lane

On 07/28/2012 03:20 PM, Edson Richter wrote:
> 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).

Well I am a little confused.
First the position marker changed from '-' to '+'
Second when I apply the above I get:
AXA VIGILANCIA for a length of 14.

Given that the substring is from 5 for .. that looks reasonable. Not
sure how you are getting TAXA... That would imply start from 4.

>
>
> Edson.
>
>


--
Adrian Klaver
adrian.klaver@gmail.com

Em 28/07/2012 20:18, Adrian Klaver escreveu:
> On 07/28/2012 03:20 PM, Edson Richter wrote:
>> 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).
>
> Well I am a little confused.
> First the position marker changed from '-' to '+'
> Second when I apply the above I get:
> AXA VIGILANCIA for a length of 14.
>
> Given that the substring is from 5 for .. that looks reasonable. Not
> sure how you are getting TAXA... That would imply start from 4.
>
>>
>>
>> Edson.
>>
>>
>
>
Right, I've posted the expression for negatives over the line example
with positive.
The correct (as your assumption) is that when (+), then start at 4. When
(-), then start at 5.
Anyway, I'm checking Tom Lane query about type of space (since \s works,
make sense to be something else than space character as I would expect).

Thanks,

Edson.

Em 28/07/2012 19:41, Tom Lane escreveu:
> Edson Richter <edsonrichter@hotmail.com> writes:
>> If I do apply trim over the substring, no spaces are removed, and I
>> cannot understand why.
>> ...
>> But using the regular expression matching "^\s*" and "\s*$" works, and
>> spaces are removed:
> I think what this means is that what you say are runs of spaces are no
> such thing, but are some other whitespace character(s).  Perhaps tabs,
> or non-breaking spaces?
>
>             regards, tom lane
>
By all means, you are right.
I supposed they are spaces... at least in the original CVS file.
I've used an Hex editor, and they are character C2A0 (UTF-8) in the file.
Now, executing the following query surprises me:

select rslinha2,
        ascii(substring(rslinha2 from 3 for position('(+)' in rslinha2)-4))
   from plan_maio
  limit 1

resulted in the following:

"2. TAXA VIGILANCIA                                   (+) R$ 13,00";160

So, file_fdw converted my original spaces into character 160.
That's the reason for trim not working.

Thanks for your toughs. You led me to the right direction: when
importing data from files, not always what looks like is what it is.


Regards,

Edson Richter.