Home Tsql: CHARINDEX and REPLACE Dosn't recognize special character (Unicode-8207)

# Tsql: CHARINDEX and REPLACE Dosn't recognize special character (Unicode-8207)

Refael
1#
Refael Published in 2017-11-14 13:27:16Z
 Given the following variable: declare @str nvarchar(50) = 'a‏bc'  between 'a' and 'b' there is a hidden character which is: nchar(8207) therefore: select len(@str) --4  and: select unicode(SUBSTRING(@str,2,1)) --8207  my problem is that I have many such records, and I have to find all these characters and delete them. I'm trying find by CHARINDEX or REPLACE but it just does not recognize this character: select CHARINDEX(Nchar(unicode(8207)),@str) --0 select REPLACE (@str , Nchar(unicode(8207)), '1') --abc 
MarkD
2#
 It seems that REPLACE() does indeed not work. Looks like you will need to use STUFF() DECLARE @Moo NVARCHAR(50) = CONCAT('a', NCHAR(8207), 'b', 'c') SELECT @Moo ,LEN(@Moo) ,LEN(STUFF(@moo, 2, 1, '')) ,STUFF(@moo, 2, 1, '')  However, this leaves you with having to know the locations of the offending unprintable characters. A WHILE loop or Tally table might serve you well here.