I am having trouble replacing or removing character “En Space” (ENSP) from a dataset. I’ve tried ltrim, rtrim & replace using both text and ASCII code 32 with no success.

When I copy the example into some test code it works:

select ltrim(rtrim('WAKE-X-AN03        ')) + '.' as example

The below replicates the issue I’m facing:

select ltrim(rtrim(N'WAKE-X-AN03        ')) + '.' as example

However, if I used the value in the database it does not and returns this:

I’m stuck as to how to get round this. I’m using SQL Server 2016