Search code examples
azure-data-lakeu-sql

Can I use Regular Expressions in USQL?

Is it possible to write regular expression comparisons in USQL?

For example, rather than multiple "LIKE" statements to search for the name of various food items, I want to perform a comparison of multiple items using a single Regex expression.

Solution

  • You can create a new Regex object inline and then use the IsMatch() method.

    The example below returns "Y" if the Offer_Desc column contains the word "bacon", "croissant", or "panini".

    @output =
SELECT 
    , CSHARP(new Regex("\\b(BACON|CROISSANT|PANINI)S?\\b"
             )).IsMatch(wrk.Offer_Desc.ToUpper())
      ? "Y"
      : "N" AS Is_Food
FROM ... AS wrk

    Notes:

    • The CSHARP() block is optional, but you do need to escape any backslashes in your regex by doubling them (as in the example above).
    • The regex sample accepts these as a single words, either in singular or plural form ("paninis" is okay but "baconator" is not).