MDS Regular expressions – awesome!

Had a report to generate for a client, got a database backup and needed to extract state abbreviations such as NSW, SA, QLD, VIC etc. from within one of 2 different records. Also needed to extract account numbers in a variety of formats, “HL Acc”, “H/L Acc:”, “HL Acc No:” and so on.

Had a fairly reasonable query running, took 57 seconds with like, charindex, substring and so on, when thought I’d try and use regular expressions to help out.

After tossing between fiddling getting CLR regex wrappers loaded up and off-the-shelf Master Data Services (see previous post on MDS and regular expressions in SQL) .. installed MDS, took about 5 minutes, and then found the same queries rewritten to use MDS running in under 1 second.

you can use regular expressions in your query such as:

and dbo.RegexIsMatch(d2.Description,N'(?<=\b.*)NSW(?=[^A-Za-z]|\s|$)’, 0)= 1)> 0 then‘NSW’

or even add named expressions

THEN dbo.RegexExtract(dp.Description,N'(?<=\b.*HO?I?/?L:?.*)(?<account>\d\d\d\d?\d?\d?)(?=.*\b)’,N’account’,1)

etc.

Advertisements