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

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#
MarkD Reply to 2017-11-14 15:58:42Z

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.312262 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO