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.

you could write a sql function that removes all non character values (or do you need numbers aswell? ) — Jeremy C., May 06 '15 at 12:48
Netezza is masrked because.. my source is sql server and target is netezza. if we can handle in netezza. then it is great. — srinath, May 06 '15 at 13:45

Christian Barron · Answer 1 · 2015-05-11T08:07:10.610

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:

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

edited May 11 '15 at 08:07

answered May 06 '15 at 12:47

Christian Barron

2,695
1
14
22

Hi Chris, your assumption is correct. It is an nvarchar column. Source is having more than one NLS code page. few examples: Mrs঩Montero, Mr቙嫁Bell, ׎׌א׊ דרקון... like hebrew language, Uk latin9 languages. when i try copy paste the special character in replace command, symbol is changing. so unable to replace. If we convert the nvarchar to varchar then there is a possibility to change the valid NLS characters. I need to replace only ঩ character. – srinath May 06 '15 at 13:41
Yes that's pretty much what converting to varchar will do. It won't allow those special characters anymore and so it will pick up ঩ character and replace it. So yes if you just do a Select Replace(cast(textct as varchar), '঩', ' ') then hopefully you should see some joy. Good luck! – Christian Barron May 06 '15 at 13:46
I need all special characters other than '঩'. because its an nvarchar column. – srinath May 06 '15 at 13:53
The function at the bottom of my update should help, you can also find similar here: http://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server – Christian Barron May 06 '15 at 14:54
Your function will only return the first 30 characters – t-clausen.dk May 07 '15 at 11:56
Why's that? Both yours and mine look the same as the link above^ – Christian Barron May 07 '15 at 12:11
because you are casting @test as varchar. . That is the same as casting it to varchar(30). Always include length on your varchar and char. Otherwise you risk getting unexpected results – t-clausen.dk May 10 '15 at 21:29

t-clausen.dk · Answer 2 · 2015-05-07T11:54:28.793

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

score 1 · Answer 3 · answered May 06 '15 at 12:39

1

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

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

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

SQL FIDDLE DEMO

answered May 06 '15 at 12:39

Rahul Tripathi

168,305
31
280
331

Although the code you copy/pasted removes special characters, it will also remove characters like the hyphen `-`, which is a valid character in hyphenated names, so may not be the best solution. The code you copied simply removes anything that isn't A-Z or 0-9. I think it will also remove spaces, which I guess wouldn't be desirable – Tanner May 06 '15 at 12:44
@Tanner:- Indeed, I guess to get the generic solution OP has to add some more ranges and checks to escape those special characters like hyphen. Else if there are some specific special characters then using `replace` function is also good. – Rahul Tripathi May 06 '15 at 12:46

score 1 · Answer 4 · answered May 06 '15 at 12:56

1

You can

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

answered May 06 '15 at 12:56

Alex K.

171,639
30
264
288

sql server replace special character with null

4 Answers4