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

Question

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

help you https://stackoverflow.com/questions/46537308/how-to-identify-unicode-text-in-sql — Yogesh Sharma, Nov 14 '17 at 13:34

score 0 · Answer 1 · answered Nov 14 '17 at 15:58

0

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.

answered Nov 14 '17 at 15:58

MarkD

5,276
1
14
22

Yeah, that's what I said. – MarkD Nov 15 '17 at 12:26

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

1 Answers1