Sql server replaces special character with null

Question

I was unable to replace the special character. Could you help me with this.

my input:

Mrs঩Montero 

the conclusion should be:

 Mrs Montero 

Special character is not displayed correctly. see image below.

Answer 1

If you just select, do the following:

 Select Replace(textct, Special_char, ' ') from mytable 

If you are updating, do the following:

 Update mytable Set textct = Replace(textct, Special_char, ' ') 

Assuming these are nvarchars, do the following:

 Select Replace(cast(textct as varchar),cast(Special_char as varchar), ' ') from mytable 

If you want to remove all special characters, you will need to use this function:

 Create Function RemoveSpecialCharacters (@text nvarchar(max)) Returns varchar(4000) AS BEGIN Declare @Return varchar(4000) = Cast(@text as varchar(4000)) While PatIndex('%[^az ]%', @Return) > 0 Set @Return = Stuff(@Return, PatIndex('%[^az ]%', @text), 1, ' ') Return @Return END Select RemoveSpecialCharacters(textct) from mytable 

Answer 2

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 

Answer 3

From source you can create a function like this:

 create function dbo.RemoveSpecialChars (@s varchar(256)) returns varchar(256) with schemabinding begin if @s is null return null declare @s2 varchar(256) set @s2 = '' declare @l int set @l = len(@s) declare @p int set @p = 1 while @p <= @l begin declare @c int set @c = ascii(substring(@s, @p, 1)) if @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122 set @s2 = @s2 + char(@c) set @p = @p + 1 end if len(@s2) = 0 return null return @s2 end 

SQL FIDDLE DEMO

And if you only have this special character, you can use this:

 SELECT replace('Mrs঩Montero', '঩', ' ') 

SQL FIDDLE DEMO

Answer 4

You can

 select textct, replace(textct collate Latin1_General_BIN, nchar(2473), ' ') from mytable