VARCHAR fields replicated with white spaces in Snowflake Azure

[rapmaster]

We've noticed certain records are replicated with white spaces in VARCHAR fields when the source (SQL Server) doesn't contain spaces. 

For example, the field is VARCHAR(5).   

Source query in SQL confirms the length of the field and data is 2 however qlik replicate replicates 5 characters (additional white space added). 

 Update: Actually the DATALENGTH returns 5 in TSQL however LEN returns 2

[john_wang]

Hello @rapmaster ,

Thanks for opening the article.

Not very sure if all the trailing whitespace in VARCHAR columns should be removed, if yes, then please try to add RTRIM() function on the column. For example RTRIM($NAME).

Regards,

John.

[rapmaster · Accepted solution]

Actually, it exists in MS SQL, however SQL automatically removes the trailing white space so it doesn't show up as an issue. 

[rapmaster]

something like this @john_wang 

[Heinvandenheuvel]

This is absolutely no error, and totally the expected behavior for expert users.

The spaces are there as you confirm (could also dump/convert to hex or append a marker char to 'see' them.

Sql server will under (too many?) circumstances try to help too much imho.

IIt will remove trailing spaces when it feels it helps but they may be there and if they are then Replicate should replicate them if it is to do an accurate job. You can then choose to remove them in a transformation but if Replicate  had removed them you could not re-invent the real value with spaces  could you now?

Please check the 'trailing spaces' comments with the LEN function in the next (and many more) reference:

https://database.guide/len-vs-datalength-in-sql-server/

Hein

[Heinvandenheuvel]

And NO you should NOT wholesale rtrim all  columns on all tables with the modified_columns_data. Imho that's just being lazy at a significant performance cost.

Just individually approach tables with this issue and put a transformation on those columns for which you believe the original source needs to be fixed up.

A better solution is to work with the source application design to remove those spaces there (if they are incorrect.  If they are correct if they are there by design vs ignorance then they should be retained). Of course I really that source app change are not likely to happen based on replication desires, but maybe for source db correctness reasons it can be fixed?