Find and Replace All Special Character in SQL

Question

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

My table design

enter image description here

 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 enter image description here

Unfortunately there is no such thing like regex replace. Wish some day microsoft will add this feature... — Giorgi Nakeuri, Jun 25 '15 at 12:17
this may help http://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server — Sachin, Jun 25 '15 at 12:18
Well, there's no RegEx which comes pre-installed. However, you can install `CLR` RegEx functions. Guidelines can be found here: https://www.simple-talk.com/sql/t-sql-programming/clr-assembly-regex-functions-for-sql-server-by-example/ I usually find them extremely useful, at least replace. — Evaldas Buinauskas, Jun 25 '15 at 12:20
@t-clausen.uk can you please link me the duplicate question here.. — Sunil Acharya, Aug 10 '16 at 07:03

score 12 · Answer 1 · answered Jun 25 '15 at 12:33

12

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)

answered Jun 25 '15 at 12:33

Galma88

2,398
6
29
50

Not getting the result can you make this without function – Sunil Acharya Jun 25 '15 at 13:15
Thanks! This worked perfectly. Does this also remove the TM and copyright characters as well? – eripey Aug 18 '15 at 22:01
Thank you its worked... i edit it before use to adjust so its suit my needs, because i still use space – kho dir Feb 27 '23 at 06:43

Mukesh Kalgude · Accepted Answer · 2015-06-26T04:02:27.710

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

Mukesh kalgude: two vote give to your answer but I'm not getting the result — Sunil Acharya, Jun 25 '15 at 13:21

Find and Replace All Special Character in SQL

2 Answers2

Linked