TSQL Replace all non a-z/A-Z characters with an empty string

Question

I would like to take a field and replace all characters that are not between a-z and A-Z with "".

Is this possible, and if so, how?

Answer 1

You could create a CLR stored procedure to do the regular expression replacement. Here's an article on that topic: http://weblogs.sqlteam.com/jeffs/archive/2007/04/27/SQL-2005-Regular-Expression-Replace.aspx

Then you could do something like this:

UPDATE your_table
SET col1 = dbo.RegExReplace(col1, '[^A-Za-z]','');

EDIT: Since CLR isn't an option, check out this link, there is a dbo.RegexReplace function there which is written in t-sql, not CLR. You could use that function in the following manner:

First, you need to run this to enable Ole:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Then create the dbo.RegexReplace function given at the link I provided.

Then you can do this:

create table your_table(col1 varchar(500))
go

insert into your_table values ('aBCCa1234!!fAkk9943');

update your_table set col1 = dbo.RegexReplace('[^A-Za-z]','',col1,1,1);

select * from your_table

Result:
aBCCafAkk

Answer 2

You could try creating a UDF (user defined function) and then use it in your queries:

• http://www.sqlteam.com/article/regular-expressions-in-t-sql (Uses COM)
• http://blogs.msdn.com/khen1234/archive/2005/05/11/416392.aspx (Uses COM)
• http://msdn.microsoft.com/en-us/magazine/cc163473.aspx (Uses CLR)

Then do a query similar to:

SELECT * FROM myTable WHERE find_regular_expression(myCol, '[^a-zA-Z]')

It also appears the there may be more native support in later versions of SQL Server, certainly 2008 R2, through the mdq.RegexMatches function (Part of Master Data Services).

http://msdn.microsoft.com/en-us/library/ee633829(SQL.105).aspx