How to find/replace weird whitespace in string

Question

I find in my sql database string whit weird whitespace which cannot be replace like REPLACE(string, ' ', '') RTRIM and cant it even find with string = '% %'. This space is even transfered to new table when using SELECT string INTO If i select this string in managment studio and copy that is seems is normal space and when everything is works but cant do nothing directly from database. What else can i do? Its some kind of error or can i try some special character for this?

Answer 1

First, you must identify the character.
You can do that by using a tally table (or a cte) and the Unicode function:

The following script will return a table with two columns: one contains a char and the other it's unicode value:

DECLARE @Str nvarchar(100) = N'This is a string containing 1 number and some words.';

with Tally(n) as
(
    SELECT TOP(LEN(@str)) ROW_NUMBER() OVER(ORDER BY @@SPID)
    FROM sys.objects a 
    --CROSS JOIN sys.objects b -- (unremark if there are not enough rows in the tally cte)
)

SELECT  SUBSTRING(@str, n, 1) As TheChar, 
        UNICODE(SUBSTRING(@str, n, 1)) As TheCode
FROM Tally
WHERE n <= LEN(@str)

You can also add a condition to the where clause to only include "special" chars:

AND SUBSTRING(@str, n, 1) NOT LIKE '[a-zA-Z0-9]'

Then you can replace it using it's unicode value using nchar (I've used 32 in this example since it's unicode "regular" space:

SELECT REPLACE(@str, NCHAR(32), '|')

Result:

This|is|a|string|containing|1|number|and|some|words.