Power of CLR (Common Language Runtime) + SQL server 2005, Managed Assembly
Partner links
Today I used Managed Assembly to use Regular Expression in SQL server 2005. I wanted to search for all records which contain special characters in my data and some foreign characters so I was using following SQL Query
Select OrganisationId,organisationname from organisation Where organisationname like ‘%-%’ OR OrganisationName like ‘%&%’ OR OrganisationName like ‘%(%’ OR OrganisationName like ‘%)%’ OR OrganisationName like ‘%£%’ OR OrganisationName like ‘%.%’ OR OrganisationName like ‘%^%’ OR OrganisationName like ‘%+%’ OR OrganisationName like ‘%@%’ OR OrganisationName like ‘%/%’ OR OrganisationName like ‘%\%’ OR OrganisationName like ‘%”%’ OR OrganisationName like ‘%#%’ OR OrganisationName like ‘%!%’ OR OrganisationName like ‘%~%’ OR OrganisationName like ‘%,%’ OR OrganisationName like ‘%<%’ OR OrganisationName like ‘%>%’ OR OrganisationName like ‘%?%’ OR OrganisationName like ‘%$%’ OR OrganisationName like ‘%`%’ OR OrganisationName like ‘%=%’ OR OrganisationName like ‘%*%’ OR OrganisationName like ‘%1%’ OR OrganisationName like ‘%2%’ OR OrganisationName like ‘%3%’ OR OrganisationName like ‘%4%’ OR OrganisationName like ‘%5%’ OR OrganisationName like ‘%6%’ OR OrganisationName like ‘%7%’ OR OrganisationName like ‘%8%’ OR OrganisationName like ‘%9%’ OR OrganisationName like ‘%0%’
But then I thought there should be other way of doing it and also I was unable to find records with foregin (çéèêëñòóôõöàáâãäåìíîïùúûüŵŷýÿ) characters included.
We were getting records in a text file which contains foregin charaters using Regex in C#. So I googled my querey and got few answers and best was to user Reqular expression in SQL query as CLR ( Common Language Runtime) allows us to do so.
I found following method on msdn
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
public partial class UserDefinedFunctions
{
public static readonly RegexOptions Options =
RegexOptions.IgnorePatternWhitespace |
RegexOptions.Singleline;
[SqlFunction]
public static SqlBoolean RegexMatch(
SqlChars input, SqlString pattern)
{
Regex regex = new Regex(pattern.Value, Options);
return regex.IsMatch(new string(input.Value));
}
};
and used in my class then I deployed my Assembly to SQL server and used the follown query with Regular Expresion.
Select OrganisationId,organisationname,Postcode from organisation left join address on organisation.addressid=address.addressid where dbo.RegexMatch(organisationName,N‘[\d\.?\-?\&?\(?\)?\£?
\^?\+?\@?\/?\''?\#?\~?\!?\,?\>?\<?\=?\`?\$?\??\*?\%?\\?
\:?\;?\¬?\}?\{?\]?\[? (çéèêëñòóôõöàáâãäåìíîïùúûüŵŷýÿ)]‘)=1
I found some help here http://www.isolutionteam.co.uk/2008/04/25/
reqular-experssion-regex-c-aspnet-vbnet for Regular expressions
I found that \d is used for all decimal numbers and \any charactr? i.e. \!? (Exclimination Mark anywhere in the field ) Or \(? (small bracket anywhere in the field ) Or \}? (curley bracket anywhere in the field). Please leave your comments telling me other way arround.
Tags: .net, Asp.net, c#, clr, common language runtime, managed assembly, RegEx, regular expression, sql server 2005, vb.net