topic Re: VARCHAR fields replicated with white spaces in Snowflake Azure in Qlik Replicate

VARCHAR fields replicated with white spaces in Snowflake Azure

rapmaster — Thu, 17 Aug 2023 09:43:16 GMT

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

Re: VARCHAR fields replicated with white spaces in Snowflake Azure

john_wang — Thu, 17 Aug 2023 10:06:52 GMT

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.

Re: VARCHAR fields replicated with white spaces in Snowflake Azure

rapmaster — Thu, 17 Aug 2023 10:57:03 GMT

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

Re: VARCHAR fields replicated with white spaces in Snowflake Azure

rapmaster — Thu, 17 Aug 2023 11:06:48 GMT

something like this @john_wang

Re: VARCHAR fields replicated with white spaces in Snowflake Azure

Heinvandenheuvel — Thu, 17 Aug 2023 16:36:15 GMT

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

Re: VARCHAR fields replicated with white spaces in Snowflake Azure

Heinvandenheuvel — Thu, 17 Aug 2023 17:05:34 GMT

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?