sql server用null替换特殊字符

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.

特殊字符未正确显示。请看下面的图片。

4 个解决方案

#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

#2

If you're simply selecting then do:

如果你只是选择那么做:

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

If you're updating then do this:

如果您正在更新,请执行以下操作:

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

Assuming these are nvarchars then do this:

假设这些是nvarchars,那么这样做:

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 a function like this:

如果要删除所有特殊字符,则需要使用如下函数:

Create Function RemoveSpecialCharacters (@text nvarchar(max))
Returns varchar(4000)
AS
BEGIN
Declare @Return varchar(4000) = Cast(@text as varchar(4000))

While PatIndex('%[^a-z ]%', @Return) > 0
        Set @Return = Stuff(@Return, PatIndex('%[^a-z ]%', @text), 1, ' ')

Return @Return
END

Select RemoveSpecialCharacters(textct) from mytable

#3

From the 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

SQL FIDDLE DEMO

And if you have only that special character then you can use this:

如果你只有那个特殊字符,那么你可以使用它:

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

SQL FIDDLE DEMO

SQL FIDDLE DEMO

#4

You can

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