topic Re: Connection to SQL Server - How to prevent leading space removal in Connectivity & Data Prep

Connection to SQL Server - How to prevent leading space removal

FrederikV — Thu, 28 Jan 2021 15:15:13 GMT

Hello,

We are using a SQL server. Some data contain leading spaces.

When I'm using the Microsoft SQL Server connector, or the ODBC connector, these leading spaces are removed. This is very annoying as we cannot distinguish data with leading spaces and data without leading space. For example, the article " 351634" and the article "351634" are considered as being the same article, which is not true.

I don't see any option to prevent this behavior, neither in the app nor in the QMC.

We also tried various drivers, without success.

What could we try?

Thanks in advance for your help

Re: Connection to SQL Server - How to prevent leading space removal

bdouden — Thu, 23 May 2024 07:11:46 GMT

Hi,

Check out this article: https://community.qlik.com/t5/Official-Support-Articles/Trailing-and-succeeding-blank-spaces-are-removed-from-data/ta-p/1711451

In the article only Qlik View is mentioned, but is works for Qlik Sense as well. Check both String values with the LEN-function.

Regards,

Boris

Re: Connection to SQL Server - How to prevent leading space removal

dennemanr — Thu, 23 May 2024 13:28:21 GMT

What I've resorted to when trying to solve problems like these is to add a character in the sql select statement like this:

sql select "A"+ItemFieldname as "ItemFieldName" from db.table;