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