CODEWITHSUNDEEP
azure-data-lakeu-sql
aaronsteers
aaronsteers

Reputation: 2571

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.

Upvotes: 0

Views: 598

Answers (2)

jraffdev
jraffdev

Reputation: 46

I'd assume it would be the same inline, but when I used regex in code behind I hit some show-stopping speed issues.

If you are checking a reasonable number of food items I'd really recommend just using an inline ternary statement to get the results you're looking for.

@output =
SELECT 
    wrk.Offer_Desc.ToLowerInvariant() == "bacon" || 
    wrk.Offer_Desc.ToLowerInvariant() == "croissant" || 
    wrk.Offer_Desc.ToLowerInvariant() == "panini" ? "Y" : "N" AS Is_Food
FROM ... AS wrk

If you do need to check if a string contains a string, the string Contains method might still be a better approach.

@output =
    SELECT 
        wrk.Offer_Desc.ToLowerInvariant().Contains("bacon") || 
        wrk.Offer_Desc.ToLowerInvariant().Contains("croissant") || 
        wrk.Offer_Desc.ToLowerInvariant().Contains("panini") ? "Y" : "N" AS Is_Food
    FROM ... AS wrk

Upvotes: 1

aaronsteers
aaronsteers

Reputation: 2571

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).

Upvotes: 1

Related Questions