Internet solutions team

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.