Oracle Unity - RegEx

Oracle Unity - RegEx

2021, Jul 08    

Use Case -> Select German speaking contacts from Switzerland

German speaking part of Switzerland is considered with ZipCodes starting with 3,4,5,6,7,8,9. All the zipcodes are 4 digits long.

In sSQL we would use zipcode LIKE '3___' OR zipcode LIKE '4___' ...

In Unity we don’t have LIKE condition. We have these options instead:

  • Matches / Does not match => for exact matches on given string
  • Matches (CSV file) => requires to provide a list of exact zipcodes (limit of 199 records currently)
  • Contains => matches on substring -> no way to limit to 4 digits only
  • Begins With => here we could specify the beginning of our criteria but no way to limit to 4 digits only
  • Matches regex => this is what is very powerful

Let’s start with selecting country = ‘SWITZERLAND’ so we then limit to zipcodes only from Switzerland: 01.png

Then use “Sniper” function to select further refinement and search for ZipCode as in the above screenshot.

Paste this regular expression: ^[3-9][0-9]{3}$ and refresh the counts: 02.png

This regular expression ^[3-9][0-9]{3}$ can be explained as follows:

  • ^[3-9] => matches any single digit 3 to 9. ^ means this digit must be at the beginning
  • [0-9] => matches any number 0 to 9
  • {3} => tells that any numbers from previous section (0-9) must occur exactly 3 times => In that way we end up with 4 characters in total.
  • $ => matches end of string. We don’t want records that are longer than 4 characters.

In the Segment Preview we can double check if our selection was right: 03.png

# unity