Data stream out from SQL Server is adding whitespace to random records

terpguy80

I've attached a screenshot of a workflow below.

There are three workflow paths and the outputs are shown in the images to the right of each path.

When I stream data out of a SQL server database table, I am finding that trailing whitespace is being added to the end of some records.  However, when I keep the data in-database or when I stream it out after applying a simple in-database length formula (but passing the field in question through as well), I see that there is no whitespace.

I am streaming out of MS SQL Server Management Studio

I've looked carefully at the source table within SQL and I see no whitespace.  An RTRIM(LTRIM([Field])) formula within SQL (not Alteryx) returns exactly 11 characters for all 18,873 records.  I've even checked the source Excel table which is the source of the SQL table data - and even the Excel file doesn't have the trailing whitespace (that I can find anyway).

Here's a short explanation of each of the three paths shown in the screenshot below:

1. Top Workflow path: the flow remains in-database , an in-db formula tool returns the SQL Len (formula shown) and then the data is summarized – there is no whitespace indicated by all records returning a len of 11
2. Middle workflow path: the flow starts in-db, the len formula is applied, and then is streamed out, the in-db len tool is summarized – again there is no whitespace (all len 11)
3. The bottom workflow path: the flow is immediately streamed out, a normal formula tool is used instead of the in-db formula tool to get the len.  Here whitespace appears in what seems to be a random set of the values.  ABrowse tool confirms that these records have whitespace.

Has anyone seen this behavior before?  

I am using Alteryx Designer Version 2020.2.3.27789 (Elevated)

BTW, yes, I'm aware that I can apply a Data Cleansing tool and eliminate the problem, but I want to understand this issue and when it might occur.  As absurd as it sounds, it almost seems like Alteryx is adding the whitespace to the records.  I seriously doubt this, if for no other reason than the random nature of which records it adds the whitespace to.  I've reviewed a few dozen of the records with the whitespace compared to those without whitespace and I don't see any discernible patterns.  

While I can't provide the actual data (it's in a database table after all), I can say it's a sequence of 11 random alphanumeric characters (A-Z, 0-9).  The numbers always end in a number.   

The SQL database data type for the field is varchar(50)

I can't think of what else might be needed to help diagnose this issue.

Thanks for any ideas or suggestions anyone can offer!

terpguy80

Well, I was able to answer my own question after consulting with a DBA and honestly I feel a little embarrassed about this but hopefully this will help others who see the same issue. 

It turns out in the SQL Server database, the white space is actually present and therefore, this doesn't appear to be an Alteryx issue.

When using LEN([Field]) in SQL Server, trailing white space is not included in the length.

However, using DATALENGTH([Field]) in SQL Server includes any whitespace in the output length value.

So, the in database path in the screenshot, simply used the databases own LEN function and ignored the whitespace.

The middle path in the screenshot, is after the in-db LEN function, so it's the situation as above.

The last path in the screenshot never used the in-db LEN function but rather the Alteryx len function.

Moral of the story: the Alteryx formula LENGTH acts like the SQL SERVER function DATALENGTH and not the SQL Server function LEN.

I hope this helps some folks out.