SQL – Replacing unwanted characters

Sometimes when you work with raw data or input by user it’s possible to have some problem with special characters. To resolve these problems we use a special feature of SQl server:  SQL CLR assembly

  1. First step is open visual studio and creating a new class library project with this code:
using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Text.RegularExpressions;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public class RegEx

{

[Microsoft.SqlServer.Server.SqlFunction

(IsDeterministic = true, IsPrecise = true)]

public static SqlString ReplaceIfMatch

(SqlString ip, SqlString exp, SqlString replace)

{

// some error handling

if (exp.IsNull || ip.IsNull || replace.IsNull)

return  SqlString.Null ;

Regex r = new Regex(exp.ToString());

//bool match = r.Match(ip).Success;

return new SqlString( r.Replace(ip.ToString(),

replace.ToString()));

}

[Microsoft.SqlServer.Server.SqlFunction

(IsDeterministic = true, IsPrecise = true)]

public static SqlBoolean IsMatch

(SqlString ip, SqlString exp)

{

// some error handling

if (exp.IsNull || ip.IsNull )

return SqlBoolean.False;

Regex r = new Regex(exp.ToString());

return new SqlBoolean( r.Match(ip.ToString()).Success);

}

// repeating for normal windows

public static string ReplaceIfMatchN

(string ip, string exp, string replace)

{

// some error handling

if (string.IsNullOrEmpty(exp)

|| string.IsNullOrEmpty(ip)

|| string.IsNullOrEmpty(replace))

return string.Empty;

Regex r = new Regex(exp);

return r.Replace(ip, replace);

}

}

Save the class library as dll in a specific folder. after that open management studio and select your db, and launch this script:

USE AdventureWorks2008R2;

GO

EXEC SP_CONFIGURE ‘clr enabled’,1;

GO

RECONFIGURE;

GO

After that reconfigure Sql server and assembly, specific privous path where you are saved the file:

CREATE ASSEMBLY

RegExBase

FROM

‘D:\<PATH>\RegExBase.dll’

WITH PERMISSION_SET = SAFE ;

GO

Last step i to create a function to use the assembly :

CREATE FUNCTION

RegExReplace

(@ip NVARCHAR(4000),@exp NVARCHAR(4000), @replaceNVARCHAR(4000) )

RETURNS NVARCHAR(4000)

AS EXTERNAL

NAME RegExBase.RegEx.ReplaceIfMatch ;

GO

 

Now we can invoke the function and see the result.

Annunci

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...