Find and Replace All Special Character in SQL [duplicate]

Question

I want to copy all row in new column with replacing all special character with -. my code is below.

My table design

 select * from mycode
    UPDATE mycode
    SET newName = Replace(myname, '%[^0-9a-zA-Z]%', '-')

It's getting copy with my code but the special character are not replaced

Result

Answer 1

Try to create this function

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

and then do your UPDATE

   UPDATE mycode
    SET newName = dbo.RemoveSpecialChars(mycode)

Answer 2

Try this query

DECLARE @specialchar varchar(15)
DECLARE @getspecialchar CURSOR
SET @getspecialchar = CURSOR FOR
SELECT DISTINCT poschar 
    FROM   MASTER..spt_values S 
           CROSS APPLY (SELECT SUBSTRING(newName ,NUMBER,1) AS poschar from mycode ) t 
    WHERE  NUMBER > 0 

           AND NOT (ASCII(t.poschar) BETWEEN 65 AND 90 
                     OR ASCII(t.poschar) BETWEEN 97 AND 122 
                     OR ASCII(t.poschar) BETWEEN 48 AND 57)

OPEN @getspecialchar 
FETCH NEXT
FROM @getspecialchar INTO @specialchar 
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE mycode 
        SET newName =Replace(myname,@specialchar,'')
FETCH NEXT
FROM @getspecialchar INTO @specialchar 
END
CLOSE @getspecialchar 
DEALLOCATE @getspecialchar