REPLACE(column, CHAR(160), '') unexpectedly clears entire string

[Rolandoc]

We are observing critical and unexpected behavior when using the REPLACE function in Microsoft Fabric Data Warehouse. Specifically, when attempting to remove CHAR(160) (Non-Breaking Space) characters from a string column using REPLACE(YourColumn, CHAR(160), ''), the entire string value is being replaced with an empty string (''), rather than just the occurrences of CHAR(160). This behavior is inconsistent with standard T-SQL REPLACE functionality and is causing severe data corruption in our transformation pipelines.

This is Fabric DWH:

This is other Azure DWH:

[V-yubandi-msft]

Hi @Rolandoc ,

Thank you for posting your query in the Microsoft Fabric Community. Before escalating the issue, it might be helpful to try a simple workaround use binary collation. This instructs the engine to compare strings byte for byte, which can help avoid issues caused by special characters like CHAR(160). Please check if this resolves the problem.

If the problem persists or you’re seeking a more permanent solution or official confirmation, as @lbendlin , suggested, it’s advisable to raise a support ticket for deeper investigation.

You can also refer to the official documentation here: CHAR (Transact-SQL) - SQL Server | Microsoft Learn

— Yugandhar
Community Support Team.

[lbendlin]

If you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues .