-
1. Re: Trim function not trimming...
shawkins Nov 19, 2016 9:26 AM (in response to m.ardito)> 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.
-
2. Re: Trim function not trimming...
m.ardito Nov 21, 2016 3:21 AM (in response to shawkins)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
-
3. Re: Trim function not trimming...
shawkins Nov 21, 2016 4:43 PM (in response to m.ardito)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.
-
4. Re: Trim function not trimming...
m.ardito Nov 22, 2016 3:27 AM (in response to shawkins)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