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?

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
– Pondlife, Commented Dec 7, 2011 at 15:19

Doug Chamberlain · Accepted Answer · 2011-12-07 14:31:25Z

13

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

This might help.
http://www.simple-talk.com/sql/t-sql-programming/clr-assembly-regex-functions-for-sql-server-by-example/

edited Dec 7, 2011 at 14:31

answered Dec 7, 2011 at 14:09

Doug Chamberlain

11.4k9 gold badges55 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

The Light Over a year ago

thanks but we have so much restriction here and we can't install assemblies into the sql server engine.

Joel Coehoorn Over a year ago

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 :(

abatishchev Over a year ago

Unfortunately the topic given is about SQL Server MS rather then SQL Server engine itself

Evan M Over a year ago

It looks like that RegEx support relates to SSMS, not to TSQL

mwigdahl Over a year ago

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.

|

Collectives™ on Stack Overflow

How to use regular expression in TSQL?

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related