How to update or remove all special characters from a varchar column

Question

I've messed around and allow users to create company names to where they're creating company names like so: Tom & Marks Ice Cream Shop.

The problem is that the company name has a link for others to click on the see the company's profile and .net is throwing a error stating that dangerous characters are not allowed.

I'm not worried about being to precise with the company name the user can update it their self when every they notice that the '&' is gone.

How can I update a column in SQL with SQL syntax to where all the special characters are removed and a space is added in the place of the special character?

Is there a way to identify in sql if there is a special character in a column value?

UPDATE [BuildingPros].[utbProfessionals]
   SET [ProfessionalName] = Replace([ProfessionalName],'some character',' ')
 WHERE ProfessionalName =ProfessionalName

Solution

This is the sample code to find string contains special character or not. You can try this one

DECLARE @MyString VARCHAR(100)
SET @MyString = 'abcdef&&&&ybyds'

IF (@MyString LIKE '%[^a-zA-Z0-9]%')
    BEGIN
       SET @MyString = Replace(@MyString,'&',' ')
       PRINT 'Contains "special" characters'
       PRINT @MyString
  END
ELSE
    BEGIN
       PRINT 'Does not contain "special" characters'
       PRINT @MyString
    END

And your UPDATE query will be like this:-

UPDATE [BuildingPros].[utbProfessionals]
SET [ProfessionalName] = 
                        (
                         CASE 
                            WHEN [ProfessionalName] LIKE '%[^a-zA-Z0-9]%' 
                                  THEN Replace(REPLACE( ProfessionalName, SUBSTRING( ProfessionalName, PATINDEX('%[~,@,#,$,%,&,*,^,&,%,*,(,)]%', ProfessionalName), 1 ),''),'-',' ')
                            ELSE [ProfessionalName]
                          END
                         )

Or You can create a function to remove special char function then call it under Update statement.

1) Removes special characters from a string value. 2) All characters except 0-9, a-z and A-Z are removed and 3) the remaining characters are returned.

 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

then call this function in update statement:-

UPDATE [BuildingPros].[utbProfessionals]
SET [ProfessionalName] = 
                   (
                     CASE 
                      WHEN [ProfessionalName] LIKE '%[^a-zA-Z0-9]%' 
                           THEN (SELECT dbo.RemoveSpecialChars(ProfessionalName))
                      ELSE [ProfessionalName]
                      END
                    )

Refer

Comments

[Filling The Stack is What I DO]

This is only for the '&', I need it for any special character.

[HaveNoDisplayName]

see modified answer, you can include any special char in here