Part 3 of Learn RegEx With Louis
In this article, we are going to take an initial look at what are referred to as “character classes” or “character sets” in Regular Expressions. They are commonly used when looking for data to be in a certain format. For example:
We are going to look at how to set a filter for 'lll-ll-lln' and/or 'lll-ll-lll' (where l is letter and n is numeric).
This is the data I will be working with, and the table can be created from the appendix of this article.
TRUNCATE TABLE dbo.RegExTest;
INSERT INTO dbo.RegExTest(Value)
VALUES ('aaa-aa-aa1'),('bbb-bb-bb2'),
('ccc-cc-ccc'),('ddd-dd-dd4'),
('eee~ee~ee5'),('ff-f-fff'),
('gggg-gg-ggg'),('justplainwrong');
--justplainwrong will not match anything,
--for a testing purpose
Like normal, I will start with a few examples of using like expressions to achieve what it can achieve both naturally, and using some more complex coding. As I started out with my discussion of using regular expressions in SQL Server (SQL Server REGEXP_LIKE features augment, not replace, LIKE. SQL Server REGEXP_LIKE features augment, not replace, LIKE)/
Hence, if you can use LIKE alone, it is usually going to be better (certainly not worse) performance since it is an operator and not a function. Then the next best method will be using LIKE along with REGEXP_LIKE if LIKE can make better use of indexes.
The Basics of character sets
To look for a character in a set (or class, but set feels a lot more natural to me), you format them using brackets. Each bracketed set references a single character, but it can be any character in the set:
[a-z]: all alpha characters[0-9]: all numeric characters[1-3]: 1, 2, or 3[1-35-7]: 1, 2, 3, 5, 6 or 7[1256]: 1, 2, 5, or 6[@8!4*]: @, 8, !, 4
And so on. I will put together a deeper dive on what all can go in these character sets, including negation, and characters that cannot be simply included in a range in a later blog. For now, suffice it to say that you can create your own this character OR this character OR … expression.
Using a LIKE expression
Using like, you can look for the format of 'lll-ll-lln' by using different character sets, in this case the first 3 characters are letters, then a -, then repeat that over and over for each character you need.
SELECT Value FROM RegExTest WHERE Value LIKE '[a-z][a-z][a-z]-[a-z][a-z]-[a-z][a-z][0-9]';
Returns:
Value ------------------------- aaa-aa-aa1 bbb-bb-bb2 ddd-dd-dd4
And if you want your output to include the ccc-cc-ccc value, along with any other values that end with a single character you could do:
SELECT Value FROM RegExTest WHERE Value LIKE '[a-z][a-z][a-z]-[a-z][a-z]-[a-z][a-z]_';
But this will include things that end with a percent sign. Now, for the sake of argument, what if couldn’t rewrite it so easily. You might do an OR condition.
SELECT Value FROM RegExTest WHERE Value LIKE '[a-z][a-z][a-z]-[a-z][a-z]-[a-z][a-z][0-9]' OR Value LIKE '[a-z][a-z][a-z]-[a-z][a-z]-[a-z][a-z][a-z]';
Both of these queries return:
Value ------------------------------ aaa-aa-aa1 bbb-bb-bb2 ccc-cc-ccc ddd-dd-dd4
Your character set can include more than one range too, like [0-9a-z]:
SELECT Value FROM RegExTest WHERE Value LIKE '[a-z][a-z][a-z]-[a-z][a-z]-[a-z][a-z][0-9a-z]';
With the extra row being the row with all ‘c’ characters. You might think that second one is not going to perform well, and that is true. But neither is the query using one LIKE expression that must start with any letter of the alphabet (unless perhaps most of the rows start with numeric values.)
Regular Expressions
Using regular expressions, we can use the same character class/set/range, but we will not have to repeat the range over and over in text if the character sets are the same.
For example, if you just want values that start with three alpha characters:
SELECT Value
FROM RegExTest
WHERE REGEXP_LIKE(Value,'[a-z]{3}-.*');
This expression says 3 alphanumeric values, then a – (dash, not an em dash, I wrote this with my own two hands on my twinkly Razer keyboard!), and then any number of other characters. So instead of repeating [a-z] manually 3 times, this makes it simpler (and also makes it more flexible because making a LIKE expression that is allows a pattern that finds '&ll&' or '&l&' starts to get messy. and you definitely would consider two LIKE expressions OR'd like '&[a-z]&'or'&[a-z][a-z]&'
That query returns:
Value -------------------------------- aaa-aa-aa1 bbb-bb-bb2 ccc-cc-ccc ddd-dd-dd4
Since they all start with 3 alpha characters, and then a dash. To finish this out, we can do the following to get all the rows that end in a number:
SELECT Value
FROM RegExTest
WHERE REGEXP_LIKE(Value,'^[a-z]{3}-[a-z]{2}-[a-z]{2}[0-9]$' );
This gives us the rows that end in a number:
Value --------------------------- aaa-aa-aa1 bbb-bb-bb2 ddd-dd-dd4
And we can include the row that ends in a letter in much the same way as we could before using LIKE:
SELECT Value
FROM RegExTest
WHERE REGEXP_LIKE(Value,'^[a-z]{3}-[a-z]{2}-[a-z]{2}[0-9a-z]$');
Multiple formats, same regular expression
But what if this was a format that we couldn’t just alter the end of the string. Clearly we could write it as an OR expression in the query. There are two versions of this I want to look at, using OR, and using variable length segments. Neither of this is straightforward using a LIKE expression.
Using OR in Regular expressions
This first example is what is going to feel natural to most of us. I want my string to look this X or Y. So we naturally write something like this:
SELECT Value
FROM RegExTest
-- ends in 2 letters and one number
WHERE REGEXP_LIKE(Value,'^[a-z]{3}-[a-z]{2}-[a-z]{2}[0-9]$' )
--ends in 3 letters
OR REGEXP_LIKE(Value,'^[a-z]{3}-[a-z]{2}-[a-z]{3}$' );
This does work and returns what is expected:
Value ------------------------- aaa-aa-aa1 bbb-bb-bb2 ccc-cc-ccc ddd-dd-dd4
We don’t try to mix the two because it can lead to errors. This may not always be the worst idea unless you are really willing to put in the test cycles. As these expressions get more and more complicated, they become harder to test, so really put them through the paces.
But there is a way we can write an OR expression in the regular expression. In the following expression, there are 2 expressions separated by a | (pipe character):
([a-z]{2}[0-9]|[a-z]{3})
The outcome of this expression is that this strings can match either:
[a-z]{2}[0-9]
or
[a-z]{3}
You can see this by executing the query with that expression added:
SELECT Value
FROM RegExTest
-- ends in 2 letters and one number
WHERE REGEXP_LIKE
(Value,'^[a-z]{3}-[a-z]{2}-([a-z]{2}[0-9]|[a-z]{3})$');
That statement returns:
Value ------------------------------- aaa-aa-aa1 bbb-bb-bb2 ccc-cc-ccc ddd-dd-dd4
Which now includes either 3 letters OR 2 letters and one number.
Variably repeating patterns
Note that I included a row that has a value of ff-f-fff. Only the last segment size matches the others, but what if we wanted match any string that has starts with 2 or 3 [a-z] characters (but not 4), then a -, we can write:
SELECT Value
FROM RegExTest
-- ends in 2 letters and one number
WHERE REGEXP_LIKE(Value,'^[a-z]{2,3}-' );
The output is:
Value ----------------------------------- aaa-aa-aa1 bbb-bb-bb2 ccc-cc-ccc ddd-dd-dd4 ff-f-fff
And if you change it to NOT REGEXP_LIKE
SELECT Value
FROM RegExTest
-- ends in 2 letters and one number
WHERE NOT REGEXP_LIKE(Value,'^[a-z]{2,3}-' );
This now returns:
Value ---------------------------- eee~ee~ee5 gggg-gg-ggg justplainwrong
Note: The next entry in the series will include the not like case in the expressions.
Something you can’t do with ranges
For alphanumeric data you can do ranges, and this typically makes great sense, but you cannot do this for non-alphanumeric data:
SELECT Value FROM RegExTest WHERE REGEXP_LIKE(Value,'[@-&]');
This will output the following error:
Msg 19300, Level 16, State 1, Line 163 An invalid Pattern '[@-&]' was provided. Error 'invalid character class range: @-&' occurred during evaluation of the Pattern.
But you can do that for individual items:
SELECT Value
FROM (values('a'),('5'),('@')) as Rows (Value)
WHERE REGEXP_LIKE(Value,'[@&]');
Which will return just the @ (at) character from my derived table.
Summary
Boy this is getting both horribly messy and very powerful. In this entry in the Learn Regular Expressions with Louis, we discussed character classes (sets/ranges) such as [0-9] which states to match a single character that is between 0 and 9 in the character set/collation being used.
We also introduced the | (or) construct that lets you filter for more than one thing in a single regular expression. And if you think we have gotten messy so far, check out one of the official email address validation regular expressions here. https://pdw.ex-parrot.com/Mail-RFC822-Address.html Sheesh.
Appendix
USE TempDB;
GO
SET NOCOUNT ON;
-- Drop the table if it exists
IF OBJECT_ID('dbo.RegExTest', 'U') IS NOT NULL
DROP TABLE dbo.RegExTest;
-- Create the table
CREATE TABLE dbo.RegExTest (
RegExTestId INT IDENTITY(1,1) CONSTRAINT PK_RegExTest PRIMARY KEY,
Value NVARCHAR(100) CONSTRAINT AK_RegExTest UNIQUE
);
Drsql.link 
Leave a comment