Louis Davidson has a few blog posts for us to catch up on. So far, this is a four-part series on regular expressions and SQL Server.
Part 1 covers simple pattern matching:
I have never once written an regular expression prior to a couple of articles on this blog. And truth be told, when I published those blogs, I got the expression wrong because it seemed to work, and it was what Copilot told me would work. If you are new like me and/or your code is important, test with lots of cases. I obviously fixed that code (thankfully the conclusions were right).
So no, I have never. LIKE does 99% of what I need in a simple manner, and .8% of the time in a complex way, so I never really thought about it too much. I suspect that will be the case even now in SQL, but like any good student, it is time to change my knowledge of regular expressions.
Part 2 covers repeating patterns:
In this blog, I want to look for strings that have 1 or more instances of a repeating pattern. For example, say you want to look for something like the following:
LIKE'%FredFredFred%'
--(or any fixed or unlimited length of a, and only a)
LIKE'%aaaaaaaaaaaaaaa%'or'%aaaaaaa%'
Part 3 looks at matching sets of characters:
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).
And part 4 deals with negation:
In Part 3, I covered some of the basics of using character classes/sets. (I do tend to say sets.) This allowed us to do things like find words that start with a, b, c, d or e. This is done using: ^[a-e] or ^[abcde]. Now I want to look at two new things (one of which looks really similar to the previous classes but does things very differently.:
- Negated character classes – Look for strings that don’t have a particular character in them
- Perl character classes – shorthand for certain types of characters
Regular expressions can be very challenging to learn and even more challenging to troubleshoot and ensure there are no missing corner cases. But they offer an enormous amount of power and that makes it all worthwhile.