sql server replace special character with null

Question

Im unable to replace an special character. Could you please help me on this.

my input is:

Mrs঩Montero

output should be:

Mrs Montero

Special character is not displaying correctly. please see the below image.

Answer 1

Create this function:

CREATE function RemoveSpecialCharacters(@Temp nvarchar(4000))
returns varchar(4000)
as
BEGIN
  DECLARE @KeepValues as varchar(100)
  -- you can add more characters like hyphen if you also want to keep those
  SET @KeepValues = '%[^A-Z0-9 ]%'
  WHILE PatIndex(@KeepValues, @Temp) > 0
    SET @Temp = Stuff(@Temp, PatIndex(@KeepValues, 1, ' ')
  RETURN @Temp
END

Now you can replace the characters.

SELECT dbo.RemoveSpecialCharacters('abc!"¤# ?123')

Result:

abc      123