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:
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:
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: