How to use regular expression in TSQL?

Question

I'd need to have a regular expression so that I can find all invalid email addresses in our database table.

SELECT *
FROM dbo.[Users] AS u 
WHERE u.EmailAddress not like 'regular expression of valid email addresses'

Does SQL Server support regular expressions? If so, how could I write one for email address?

http://stackoverflow.com/questions/229824/tsql-email-validation-without-regex — EricZ, Dec 07 '11 at 14:28
[Don't use regular expressions to validate email addresses.](http://stackoverflow.com/questions/1903356/email-validation-regular-expression/1903368#1903368) — Martin Smith, Dec 07 '11 at 14:34
Both regular expressions in TSQL and regular expressions for validating emails have been discussed numerous times on this site. What information do you need that is not already answered elsewhere? — Pondlife, Dec 07 '11 at 15:19
possible duplicate of [TSQL Like regular expression](http://stackoverflow.com/questions/3259449/tsql-like-regular-expression) — Peter O., Mar 18 '13 at 20:07
@MartinSmith I think you meant : http://stackoverflow.com/questions/201323/using-a-regular-expression-to-validate-an-email-address — AFract, May 22 '16 at 08:03

Doug Chamberlain · Answer 1 · 2011-12-07T14:31:25.880

13

T-SQL does not support regular expressions. You can however create a .net CLR function that will add this ability.

edited Dec 07 '11 at 14:31

answered Dec 07 '11 at 14:09

Doug Chamberlain

thanks but we have so much restriction here and we can't install assemblies into the sql server engine. – The Light Dec 07 '11 at 14:18
1

You had me there for a minute, but reading the link it looks like that will only work for the find/replace dialog in management studio, not a as part of a query :( – Joel Coehoorn Dec 07 '11 at 14:21
Unfortunately the topic given is about SQL Server **MS** rather then SQL Server engine itself – abatishchev Dec 07 '11 at 14:21
It looks like that RegEx support relates to SSMS, not to TSQL – Evan M Dec 07 '11 at 14:22
2

That SQL 2012 link is just talking about regexes in SSMS -- not anything new built into SQL Server. William, if you can't install assemblies you are not going to be able to use regexes. You'll have to roll your own as best you can using SQL Server's intrinsic string functions. – mwigdahl Dec 07 '11 at 14:22
@Joel I've proposed an idea on [SQL Server UserVoice](http://mssqlconn.uservoice.com/forums/113295-feature-feedback/suggestions/2437316-fully-support-regex-in-sql-server-engine) board – abatishchev Dec 07 '11 at 14:28

1 Answers1