searching for and updating a wildcard/unicode chacracter using SQL

I have a string stored in a SQL databse that contains a wildcard unicode character followed by whitepsapce before the text I suspect in an attempt to try and mock right to left text and I need to remove the wildcard character however attemptng to search for the characters with a like clause does not locate it correctly.

I have tried using the square brackets method too combined with the like clause and this returns to me nothing. I have hundreds of these that I need to update.

                                                           يستمع المتعلم بانتباه مُتَّبعا توجيهات مكونة من خطوة أو خطوتين.

​x
 
                                                           يستمع المتعلم بانتباه مُتَّبعا توجيهات مكونة من خطوة أو خطوتين.​

The character is actually viewed as a small box with a question mark inside of it although when copied and then pasted it’s simply small box as per the example above.

UPDATE
[Application].[Database].[Table]
set
[Name] = REPLACE ([Name], N'%%',  N'% %')
where 
[Name]  LIKE N'%%'

 
UPDATE[Application].[Database].[Table]set[Name] = REPLACE ([Name], N'%%',  N'% %')where [Name]  LIKE N'%%' ​

The above query currently does not update the value by removing the unwanted character.

Is there any way to replace this wildcard chracter using only SQL?

Answer

REPLACE ([Name], N'%%',  N'% %')

 
REPLACE ([Name], N'%%',  N'% %')​

looks for the substring '%%'. This does not exist in the string however. You want

REPLACE ([Name], N'',  N' ')

 
REPLACE ([Name], N'',  N' ')​

instead. This replaces all '' with blanks.

Advertisement

Answer