Jerry Nixon on Windows: tSQL Regular Expressions

Jerry Nixon on Windows

Saturday, February 20, 2010

tSQL Regular Expressions

Did you know that you can use Regular Expressions in tSQL? Welp, you certainly can. The key is to use the LINK operator and the [square brackets] to indicate patterns.

Now, don’t get too excited. You can’t use the full suite of syntax (which most people don’t know anyway). You can certainly accomplish a lot.

There’s one thing to remember: this is NOT RegEx. That’s important because RegEx is the fastest string parser in the universe. This only mimics the syntax. Don’t expect its performance. Having said that, I have tested it against LIKE wildcards (%) and it SMOKES them.

But check out what you can do:

image

Here are the results:

image

But it doesn’t seem to operate exactly how you would expect:

  1. As you can see [A-Z] works correctly, but [a-z] seemed broken.
  2. See how [ ] finds a space, but also an empty space (not expected).

(I am using SQL 2008 Developer Edition)