Trim function not trimming...

[m.ardito]

Hi,

I recently found that on a text field, type string, the function trim, rtrim seems to be ineffective...

while this works,

select
'-' || trim ('test ') || '-'
from contatti
limit 1

and gives, of course a result = -test-

On a real field, where I have sometimes one or more spaces appended to a value  (don't ask...)

An example:

select
'-' || c.inout || '-' as base_field,
'-' || rtrim(c.inout) || '-' as rtrimmed,
'-' || trim(c.inout) || '-' as simple_trimmed,
'-' || trim(BOTH ' ' FROM c.inout) || '-' as both_trimmed
from contatti as c
where c.inout is not null

Results:

base_field  =  -In entrata   -  

rtrimmed  =  -In entrata   -  

simple_trimmed =  -In entrata   -  

both_trimmed =  -In entrata   -

What could be the cause? It seems the function does not see a "blank" char at the end (there are at least 2, it seems)

Could it be a backend (mssql server) collation problem? Or what?

Of course I could correct the source application which is inserting wrong strings, and then correct old database fields, but I wish to understand why here trimming doesn't work...

Teiid 9.02, atm.

Marco

[shawkins]

> What could be the cause? It seems the function does not see a "blank" char at the end (there are at least 2, it seems)

Can you issue a query with rtrim directly against the database to confirm that you are seeing the same behavior regardless of Teiid.

> Could it be a backend (mssql server) collation problem? Or what?

trim/trim both are being pushed as rtrim(ltrim(expr)), so if there is an issue with rtrim it would make sense that it shows up there as well.  Yes, if what appears to be a blank character is not exactly a space, then yes trim won't remove it. 

[m.ardito]

Steven Hawkins ha scritto:

> What could be the cause? It seems the function does not see a "blank" char at the end (there are at least 2, it seems)

Can you issue a query with rtrim directly against the database to confirm that you are seeing the same behavior regardless of Teiid.

Confirmed, even on the SQL server itself this does not work... grrr.

The field is there defined as nvarchar 50 with collation "database default"... I will dig on tha t"not exactly a space" data appended...

Do I have any tool on Teiid side to investigate about that "not exactly a space" data ? And maybe force an on-the-fly conversion?

On that server I'm on a journey to convince owners to correct several app/db implementation errors, like different collations on joining fields , and more...

Marco

[shawkins]

The Teiid/SQL Server ascii function - ASCII (Transact-SQL) could be used to get the ascii code for the character.  It needs to be 32 for trim to remove it as you expect.  You could end up needing to use replace or even something more general like regexp_replace to get rid of all whitespace.

[m.ardito]

Thanks. It turns out, it could be a (very old, .net 1. iirc) web application that apparently, somewhere (not everywhere) adds an ascii 160 character to that field's value, which is an html  "numeric entity" reference for "non-breaking space", aka "& nbsp;". Note, the "space" is added to a value which is selected by users through a list, so it shouldn't be there anyway, imho. It's one of the weirdest apps I've ever seen, no documentation about app, code or db, weird choices for app and db, hundreds of aspx/vb pages ...a real PITA. I'll do my best bit for this but sure Teiid is doing the right thing here...

references: http://stackoverflow.com/questions/3274315/is-160-a-replacement-of-nbsp

html - Is " " a replacement of " "? - Stack Overflow

HTML Document Representation

HTML Codes - Table of ascii characters and symbols