topic Case Insensitivity in SQL Server in Qlik Compose

Case Insensitivity in SQL Server

ricatorrespri — Fri, 14 Mar 2025 20:49:07 GMT

We have an attribute, for example PERSON_NAME, that sometimes changes case in the source. For example, the person name is "JOHN", but then later changes to "John".

Compose does not detect this change because due to SQL Server columns being case insensitive, it sees that JOHN = John. Therefore, the name change does not flow through to the Data Warehouse and Data Mart.

We thought of altering the collation of PERSON_NAME to SQL_Latin1_General_CP1_CS_AS. This would have to be done on the TSTG table and the target data warehouse table.

However, since the TSTG table is dropped and recreated on each run, then the collation goes back to the default case insensitive.

Is there another recommended way to deal with this situation?

Thanks in advance.

Re: Case Insensitivity in SQL Server

sureshkumar — Tue, 26 Sep 2023 12:00:48 GMT

Hello @ricatorrespri

As checked with our internal team

As an ELT solution, we adhere to the collation of the target database.

The recommended approach here is to leverage the database collation to support the type of processing etc.

Another choice is to create a specific field that concatenates and hashes certain columns. (eg an SHA hash of John and JOHN equate to different values. Compose will see this as a change to the data – and type 2 the record.

Regards,

Suresh

Re: Case Insensitivity in SQL Server

goro23 — Mon, 09 Oct 2023 15:46:14 GMT

this is considered the correct solution for pretty much all instances of this issue in computer science. though most people convert to lower... converting to upper will get you some weird looks, but it'll work just as well.