DevHeads.net

Trim not working (PostgreSQL 9.1.2 on Win64)

By Edson Carlos Er... at 07/27/2012 - 19:58

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,

Comments

Re: Trim not working (PostgreSQL 9.1.2 on Win64)

By Adrian Klaver at 07/28/2012 - 12:46

On 07/27/2012 04:58 PM, Edson Richter wrote:
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.

Re: Trim not working (PostgreSQL 9.1.2 on Win64)

By Edson Richter at 07/28/2012 - 18:20

Em 28/07/2012 13:46, Adrian Klaver escreveu:
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:

or

But using the regular expression matching "^\s*" and "\s*$" works, and
spaces are removed:

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.

Re: Trim not working (PostgreSQL 9.1.2 on Win64)

By Adrian Klaver at 07/28/2012 - 19:18

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

Re: Trim not working (PostgreSQL 9.1.2 on Win64)

By Edson Richter at 07/28/2012 - 20:53

Em 28/07/2012 20:18, Adrian Klaver escreveu:
Thanks,

Edson.

Re: Trim not working (PostgreSQL 9.1.2 on Win64)

By Tom Lane at 07/28/2012 - 18:41

Edson Richter < ... at hotmail dot com> writes:
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

Re: Trim not working (PostgreSQL 9.1.2 on Win64)

By Edson Richter at 07/28/2012 - 21:24

Em 28/07/2012 19:41, Tom Lane escreveu:
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.