SQL Saturday #200 - Philadelphia, PA - 2013 Learn RegEx in T-SQL

SQL Saturday #200 - Philadelphia, PA LogoSQL Saturday #200 in Philaddelphia, PA on Saturday June 1, 2013 had a nice turn out, good lunch, and at least the ones I was able to attend - good sessions.

For thoes just looking for the code and slides here is a link to a zip file with everything from the Learn RegEx In TSQL & Kick as{2}|a[s][s]+

About 20 people attended, most of whom left very nice feed back.  It was nice to see a few familliar faces from the Philadelphia SQL Server Users Group.  But also good to see a whlo new group of faces.

SQL Saturday #200 - Philadelphia - RegEx Class - 2013

Data Whisperer: Detecting & Extracting Meaning from Changing Data Presentation at Philly .NET Code Camp 2012.2

Philly .NET Code Camp 2012.2

My thanks to Philly .NET and PSSUG for inviting me to speak at Code Camp 2012.2 on Saturday November 17, 2012.  Additional thanks to all thouse who attended either of the presentations.

Download the code and presentation:   Data Whisperer - Detecting & Exctracting Meaning From Changing Data.zip

 The folks at the Microsoft Technology center for Philadelphia recorded the presentations at the PSSUG meeting on Wednesday November 7, 2012.  You can see Mark Kormer on Big data and this presentation here at The Philadelphia MCT Community Portal.

Learn RegEx in T-SQL & kick as{2}|a[s][s]+

Thanks to all who attended the Learn RegEx in TSQL presentation at the Philly.Net Code Camp 2011.2.  Especially those who took the time to catch up with me throughout the day.

RegEx is a big topic and is hard to learn in only an hour and 20 minutes. I do hope that you got enough information to get yourself started in learning how to use RegEx in TSQL in your own environment.

All the links discussed in the presentation are included throughout this post. The PowerPoint was not included since it seemed kind of pointless without me also attached to the presentation. Some of the content was extracted and included in this post.

The Class!

Code Camp Heroes Bill and Rob

Code:

Down Load The code for the presentation.

The file 01 - installRegexAssembly.sql is my version of the create assembley and create of the RegEx functions in the [util] schema.

Goal:

Get you started using RegEx in TSQL

• Load the toolbox with techniques 
• Exposure to variations 
• Under the hood knowledge

Warning

•  There are many ways to write a Regular Expression!
• RegEx isn’t inherently better then other solutions, even in TSQL.

What Are Regular Expressions?

Tools for matching and manipulating text RegEx pattern = as{2}

Will Match

• ass
• assume
• passenger
• class

Will NOT match

• fast
• cake
• salad

Why use RegEx?

Over standard TSQL RegEx can:

• Be faster
• Be easier to code
• Require fewer steps
• Accomplish more complex tasks

What can RegEx be used for?

• Validate Data Format 
• Scrub Data 
• Same as standard TSQL text functions but allow for more complex criteria 
  • Replace() 
  • LIKE ‘%’  --Technically not a function but you get the idea
  • PATINDEX() 
  • SUBSTRING()

How to use RegEx in TSQL?

Formula for RegEx in TSQL

SQL 2005 and up supports .Net CLR for User Defined Functions

.Net has built in base class library for RegEx: System.Text.RegularExpressions

Combine above two facts -> RegEx in TSQL

Now how do we actually use RegEx in TSQL?

Write the code for the .NET based UDFs ourselves 

OR

Use someone else's code!

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

The is an excellent article by Phil Factor over at Simple-talk.  There are a few parts to this article including the basics of writing .NET based functions in TSQL.  There is also a file that lets you jump start using RegEx in TSQL.

Keep in mind you will need to be running SQL 2005 or newer for this code to work.  It will also work on express versions of SQL server too.  The CLR will also need to be enabled for the database you want to install the RegEx functions.

 There are 4 files at the top of the article you can download:

1. InstallRegex.sql
2. installRegexAssembly.sql
3. installRegexSample.sql
4. RegexSQLCLR.vb

The fourth file is a text file with the VB code that gets compiled in to the DLL.  You do not need to do this unless you are curious and interested in the details.

To jump right in to using the TSQL UDFs in your data base just run the first to files.  The first one, despite its name creates the Assembly on the SQL db.  The second creates the functions in TSQL that are implemented by the code in the assembly.

NOTE: The code linked to above installs the functions in the [dbo] schema.  The version used in the presentation was modified to create a new Schema [util] and install the assembly and functions under that schema.

Overview of the RegEx Functions

The functions installed by the SQL scripts will create 9 funtions that will be useable in your TSQL code.  The break down as follows based on return type:

• Boolean 
  • RegExIsMatch() 
• Text Returning 
  • RegExReplace () 
  • RegExReplaceX ()
  • RegExMatch()
• Table Valued 
  • RegExSplit() 
  • RegExMatches () 
• Integer 
  • RegExIndex() 
• RegEx Utility 
  • RegExEscape() 
  • RegExOptionEnumeration()

Tips for Learning RegEx patterns

Have a good cheat sheet. Here are some I've used

http://www.regular-expressions.info/reference.html

http://regexlib.com/CheatSheet.aspx

http://regexlib.com/DisplayPatterns.aspx This link is to a libray of expressions for matching common patterns.

Presenting at Philly.Net Code Camp 2011.2 Oct 15

I will be presenting at Philly.Net Code Camp 2011.2 on Oct. 15 http://goo.gl/yYGXv

Presentation will be:  Learn RegEx in T-SQL & kick \

Session Description: (This is what users will see, please go for concise and elegant) Regular Expressions, aka RegEx, is a powerful syntax for finding patterns in text, which has been around since the 60’s. Until recently the power of RegEx has been an elusive tool to T-SQL Developers. This presentation will provide an introduction to Regular Expressions in general and concentrate on the tools and techniques for using them in T-SQL. Find out how easy and powerful Regular Expressions can be for everyday tasks such as basic where condition criteria to validating and scrubbing data. Come on you know you’ve always wanted to learn RegEx ever since you first saw them. Now is your chance to get a jumpstart on using them. You will leave with the tools, knowledge and resources to get you started.

Data Modeling Software Tools List

Embarcadero's ER/Studio has been my data modeling tool of choice for over a decade.  There are many good things about the product but there are also things I don't like or wish were better or different.   It is also an expensive software package, approximately $2K for a SQL server only version plus around $800 for annual maintainence including upgrades.

Roughly once a year I get a hankering to check out the grass on the the other side of the fence.  This hankering hit me a few weeks ago.  Google brought me to the following website that had a nice list of tools with a brief description and cost:

http://www.databaseanswers.org/modelling_tools.htm

A few of the tools caught my eye and I downloaded and tested a few.  The short answer is I'm sticking with my trusted albeit not perfect ER/Studio.  The good news is the quality and sophistication of the affordable tools has improved greatly.  Here is a list of a few of the tools that made the short list:

ModelRight 3.6

dbconstructor

DataArchitect

DeZine for Databases

When I did my testing I didn't document my findings and impressions of these packages for the purpose of posting the information.  If I needed a tool to get some work done without spending a lot of money there are definitely a few out there that would be useful, but there are no must have superstars in the pack.