How to stop Alteryx from trimming trailing spaces from in-database table (Oracle)?

Question

Hi guys,

Simple question but there seems no solution?

We have a field with a trailing space in Oracle (yeah i know but that's for datamanagement). From the moment we load the field in Alteryx with either the data input tool or the in-db tool, Alteryx trims the trailing space.

Is there any way to stop Alteryx from doing this?

The funny thing is that you obviously have to query the field in the data input/in db tool with the trailing space (example: 'field ') so Alteryx somehow decides on itself to trim the field before throwing it in the user interface of designer.

Thanks!

Greetings,

Seb

Sebastiaandb · Answer

Hi @BenMoss thanks for looking at my question!

I had to specify my question better. It's not the field's name, the row values are corrected whenever we get the data into Alteryx.

Here are two screenshots of the same data (1 from Golden and 1 from Alteryx).

You might be correct regarding the driver, i can simulate that by using OLEDB instead of OCI later this week (although i would never actually pick OLEDB over OCI it's just fun to test). I'll keep you updated!

Screenshot from Golden (query)

Screenshot from Alteryx

BenMoss · Answer

Hi @Sebastiaandb I just had a play around with this in an attempt to replicate this issue, this post I found may be helpful...https://michaeljswart.com/2014/09/sql-server-ignores-trailing-spaces-in-identifiers/

Based on the above article, and some testing... select "bob" from table and select "bob " from table are seen as the same statement, and both will successfully return me data.

However, with MS SQL (which I am using, I appreciate Oracle is a very different beast), if I do select "bob" then I get a field called "bob" in Alteryx, if I do select "bob " then I get a field called "bob " in Alteryx, so from that view point, I can't replicate the issue as the space is returning as expected (even if I do select * I get the space).

May I ask how you are confirming that there is no space in the header? If you could share screenshots I'm sure that would help (in the image I share above you can see the trailing space is being kept in the name).

I believe it isn't Alteryx that is doing the trimming, but rather the driver you are using to connect to the database (I could be well off the mark there, but the driver is the 'middle man' between the two platforms.

Ben