Remove weird characters ( A with hat) from SQL Server varchar column

Solution 1:

You can use .net regular expression functions. For example, using Regex.Replace:

Regex.Replace(s, @"[^\u0000-\u007F]", string.Empty);

As there is no support for regular expressions in SQL Server you need to create a SQL CLR function. More information about the .net integration in SQL Server can be found here:

• String Utility Functions Sample - full working examples
• Stairway to SQLCLR - still in progress
• Introduction to SQL Server CLR Integration - official documentation

In your case:

1. Open Visual Studio and create Class Library Project:

   

2. Then rename the class to StackOverflow and paste the following code in its file:

   using Microsoft.SqlServer.Server;
   using System;
   using System.Collections.Generic;
   using System.Data.SqlTypes;
   using System.Linq;
   using System.Text;
   using System.Text.RegularExpressions;
   using System.Threading.Tasks;
   
   public class StackOverflow
   {
       [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true, Name = "RegexReplace")]
       public static SqlString Replace(SqlString sqlInput, SqlString sqlPattern, SqlString sqlReplacement)
       {
           string input = (sqlInput.IsNull) ? string.Empty : sqlInput.Value;
           string pattern = (sqlPattern.IsNull) ? string.Empty : sqlPattern.Value;
           string replacement = (sqlReplacement.IsNull) ? string.Empty : sqlReplacement.Value;
           return new SqlString(Regex.Replace(input, pattern, replacement));
       }
   }
   

3. Now, build the project. Open the SQL Server Management Studio. Select your database and replace the path value of the following FROM clause to match your StackOverflow.dll:

   CREATE ASSEMBLY [StackOverflow] FROM 'C:\Users\gotqn\Desktop\StackOverflow\StackOverflow\bin\Debug\StackOverflow.dll';
   

4. Finally, create the SQL CLR function:

   CREATE FUNCTION [dbo].[StackOverflowRegexReplace] (@input NVARCHAR(MAX),@pattern NVARCHAR(MAX), @replacement NVARCHAR(MAX))
   RETURNS NVARCHAR(4000)
   AS EXTERNAL NAME [StackOverflow].[StackOverflow].[Replace]
   GO
   

You are ready to use RegexReplace .net function directly in your T-SQL statements:

    SELECT [dbo].[StackOverflowRegexReplace] ('Hello Kitty Essential Accessory Kit', '[^\u0000-\u007F]', '')

    //Hello Kitty Essential Accessory Kit

Solution 2:

if you are looking for alphabets and numbers only in a string, than this can help you out.

In this, Regex is used to replace all characters other than alphabets and numbers.

Solution 3:

This seems to work:

string input = "Hello Kitty Essential Accessory Kit";
string res = Regex.Replace(input, @"[^a-zA-Z0-9\s]", "");

Console.WriteLine(res); // Hello Kitty Essential Accessory Kit