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 ,

Just following up to see if your issue was resolved through the Microsoft support ticket. If so, it would be helpful if you could share the solution or any key points here, as it may benefit others with similar questions.
If there’s no update, we’ll plan to close this thread soon. For future questions, please feel free to start a new post in the Microsoft Fabric Community Forum. We’re here to help.

Thank you for your participation in the community.

[V-yubandi-msft]

Hi @Rolandoc ,

Does the solution provided address your issue, or are you still experiencing the problem?
Your feedback is important to the community. If your issue has been resolved, please mark the response as Accepted Solution to help others find it more easily.

Thank you.

[Kanstantsin01]

Just try NCHAR instead of CHAR.

[V-yubandi-msft]

Hi @Rolandoc ,

Thank you for posting your query in the Microsoft Fabric Community. Before submitting a support ticket, it may be useful to try a simple workaround by using 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 .