Regular Expressions in SQL Server Databases: Implementation & Use

An error occurred trying to load this video.

Try refreshing the page, or contact customer support.

Coming up next: Practical Application for Database Programming: Regular Expressions

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:04 Regular Expression Definition
  • 1:24 Using Regular Expressions
  • 6:14 Case Sensitivity
  • 7:10 Matching Number Patterns
  • 8:21 Complex Matching
  • 8:47 Lesson Summary
Save Save Save

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Log in or Sign up

Timeline
Autoplay
Autoplay
Speed Speed
Lesson Transcript
Instructor: Ee Teik OH
A regular expression is simply a sequence of characters or a pattern. SQL databases contain different types of data such as strings, numeric, images as well as other unstructured data. Queries in SQL often need to return data based on regular expressions. This lesson outlines how this can be done.

Regular Expression Definition

A regular expression is similar to a rule which defines the characters that can appear in an expression. In SQL databases, selecting field values based on regular expressions can be very useful. One example would be to validate credit card numbers or telephone numbers from a given database field. Another would be to check numeric values matching a particular sequence, such as integers containing the digits 99 or 88. SQL server database implementations provide built-in regular expression support.

This figure contains test data we will use for our discussion. This table contains details of all respondents to a simple survey. The RespondentFullName field contains the name of the respondent, the ContactNo field contains the contact telephone number, the Answer field contains the respondent's agreement to be contacted for additional comments, and the LuckyDrawNumber field contains the lucky draw number assigned to the respondent for their participation incentive.


Diagram01


Note in here that SQL Server doesn't include a full-blown REGEX function, which MySQL includes. True regex operations in SQL Server require additional code and plug-ins. The expressions we will be using are attached to the LIKE operator in our queries. This still achieves the same goal, but it isn't a true 'regex' per se.

Using Regular Expressions

Let's first take a look at some simple examples:

Consider the simple query to find all records whose respondent's name begins with letter P:


SELECT * FROM Survey WHERE RespondentFullName LIKE 'P%';


What if we need the respondent names which contain and not just start with the letter P? The following can be used:


SELECT * FROM Survey WHERE RespondentFullName LIKE '%P%';


These two examples require some form of pattern matching to be performed in order to select the required records, and SQL wildcards can be used. Regular expressions, like wildcards, are used to define patterns but allow for more complex pattern matching. The SQL statements can thus be replaced respectively by:

Starts with P:


SELECT * FROM Survey WHERE RespondentFullName LIKE '[P]%';


Contains P:


SELECT * FROM Survey WHERE RespondentFullName LIKE '%[P]%';


Here, [P]% and %[P]% are two simple regular expressions. [P]% means any string beginning with the letter P and can contain any number of characters after this. [P] means matching for one character only. %[P]% means that the string can begin with anything and end with anything and must have at least one P in it. Even if a string is only one character long with the letter P, it matches this regular expression.

With regular expressions, anything within an opening [ and closing ] means that the characters within are to be used for the matching process. Regular expressions are often referred as regex, and from this point forward, the term regex will be used.

The following are some regex examples:

Regular Expression Description
. Match any one character
* Match any character
+ Match at least one instance of the expression before
^ Start at beginning of line
$ Search at end of line
< Match only if word starts at this point
> Match only if word stops at this point
\n Match a line break
[] Match any character within the brackets
[^...] Matches any character not listed after the ^
[ABQ]% The string must begin with either the letters A, B, or Q and can be of any length
[AB][CD]% The string must have a length of two or more and which must begin with A or B and have C or D as the second character
[A-Z]% The string can be of any length and must begin with any letter from A to Z
[A-Z0-9]% The string can be of any length and must start with any letter from A to Z or numeral from 0 to 9
[^A-C]% The string can be of any length but cannot begin with the letters A to C
%[A-Z] The string can be of any length and must end with any of the letters from A to Z
%[%$#@]% The string can be of any length and must contain at least one of the special characters enclosed in the bracket

Note this query:


SELECT RespondentFullName, ContactNo FROM Survey WHERE RespondentFullName LIKE '%[A]%';


Here, the regular expression is a string pattern of any length with at least one occurrence of the letter A. When the following query is executed, the output is as shown in this diagram:


Diagram02


Notice that the result values from the RespondentFullName field show up irrespective of case. We have not yet considered case sensitivity so far in regex, and we will do this in the next section.

Next, consider the following SQL statement:


SELECT RespondentFullName, ContactNo FROM Survey WHERE RespondentFullName LIKE '%[^P]%';


When this statement is executed, the results are shown in this image. Here, the regex '%[^P]%' means any string expression of any length which contains at least one letter which is not the letter P.


Diagram03


To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support