{"id":306,"date":"2019-06-22T18:00:50","date_gmt":"2019-06-22T18:00:50","guid":{"rendered":"https:\/\/datablog.roman-halliday.com\/?p=306"},"modified":"2024-10-09T06:21:55","modified_gmt":"2024-10-09T06:21:55","slug":"querying-sql-server-with-something-like-a-regular-expression","status":"publish","type":"post","link":"https:\/\/datablog.roman-halliday.com\/index.php\/2019\/06\/22\/querying-sql-server-with-something-like-a-regular-expression\/","title":{"rendered":"Querying SQL Server with something LIKE a regular expression"},"content":{"rendered":"\n

Most of us are familiar with the LIKE<\/a> command in SQL Server (and other DBMS environments), but few venture beyond simple text matching and the ‘%’ wildcard. It’s ability to use some regular expression (a.k.a. regex or regexp) syntax is a surprise to many.<\/p>\n\n\n\n

For this article, I’m assuming that readers have already used the LIKE statement a few times and the first example is almost patronising.<\/p>\n\n\n\n

Basic Text Substring Matches<\/h2>\n\n\n\n

Let’s start with what most people use, a simple text substring match. Using the ‘%’ to say “and anything else”… <\/p>\n\n\n\n

WITH sample_data AS (\nSELECT 1   AS [id], 'abc' AS [val] UNION ALL\nSELECT 2   AS [id], 'ABC' AS [val] UNION ALL\nSELECT 3   AS [id], 'xyz' AS [val] UNION ALL\nSELECT 4   AS [id], 'XYZ' AS [val] UNION ALL\nSELECT 5   AS [id], '123' AS [val] UNION ALL\nSELECT 5   AS [id], '1T3' AS [val]\n)\nSELECT *\n  FROM sample_data\n WHERE [val] LIKE 'ab%'\n;<\/pre>\n\n\n\n
Which gives us the below output:<\/p>\n\n\n\n
| id  | val |\n| 1   | abc |\n| 2   | ABC |<\/pre>\n\n\n\n
While powerful (and the most used feature of LIKE), this just the tip of the iceberg. It’s also a perfect example of where the syntax of the LIKE command is different from the equivalent regular expression.<\/p>\n\n\n\n
Regular Expressions<\/h2>\n\n\n\n
Some people will read “regular expression” and react with a knowing nod (feel free to jump ahead), everyone else is about to learn something you never realised you needed to know. <\/p>\n\n\n\n
What is a Regular Expression?<\/h3>\n\n\n\n
A regular Expression is a string which represents a pattern for matching. There are many cases where there is a pattern which we want to test text against. A common example is if a date is formatted as a process expects it (YYYY-MM-DD or DD\/MM\/YY etc…).<\/p>\n\n\n\n
A more advanced case is validating an email address, very quickly you can build up rules in your head (it has to have the ‘@’, but only once, and there has to be something either side of it…. and so on)<\/p>\n\n\n\n
What do they do in SQL Server?<\/h3>\n\n\n\n
The common one we have all seen is shown in the first bit of code at the top, ‘ab%’ (the letter ‘a’ followed immediately by the letter ‘b’ then “anything else”). The syntax of a regular expression allows us to build more complicated rules, such as ‘a’ or ‘b’, followed by a number (less than 5), and then any letter.<\/p>\n\n\n\n
For illustrative purposes, here is a sample of the two expressions mentioned above (don’t worry about understanding them just yet).<\/p>\n\n\n\n
--the letter 'a' followed immediately by the letter 'b' then \"anything else\"\n   AND [val] LIKE 'ab%'\n\n--'a' or 'b', followed by a number (less than 5), and then any letter.\n   AND [val] LIKE '[ab][0-4][a-z]'<\/code><\/pre>\n\n\n\n
I’ve put some links to documentation on regular expressions at the bottom. I believe they are something that every developer should have a basic understanding of. Just keep in mind the syntax in SQL Server is a little different (and cut down) than most other languages.<\/p>\n\n\n\n
Using Regular Expressions in SQL Server<\/h2>\n\n\n\n
Now we know what a regular expression is, it’s time to look at the syntax. There are only 4 sets of wild card characters used in the LIKE statement, but combined they are very powerful.<\/p>\n\n\n\n