Alma Analytics Regular Expression Examples

Note that if you do copy/paste here make sure the quotation marks transfer correctly.

Often when creating an integration with an Alma Analytics report it is useful to use a regular expression in the “edit formula” field in order to be able to filter by values which do or do not have certain strings

These integrations might schedule an Alma Analytics report to an FTP server, or perhaps use an API to retrieve an Alma Analytics report.

The regular expressions below are examples used in the “edit formula” of the “Primary Identifier”.  They return a “1” in certain cases and a “0” in other cases.

In this way the field can be filtered by “= 1” or “= 0” in order to get particular values.

While these examples are using the primary identifier, the same logic can be applied to other fields as well.

At the end of this blog you will see an example using one of the regular expressions

Example 1: Get field which has all numeric characters.  This will return “1” if the string is all numbers and “0” if there is at least one alphabetic character

evaluate('REGEXP_INSTR(%1, ''^[0-9]+$'')',"User Details"."Primary Identifier")

Example 2: Get field which has all alphabetic characters.  This will return “1” if the string is all alphabetic characters and 0 if there is at least one number in the string

evaluate('REGEXP_INSTR(%1, ''^[A-z]+$'')',"User Details"."Primary Identifier")

Example 3: Get field which starts with numeric characters and ends alphabetic with characters.  This will return “1” if the string starts with numbers and ends with characters and “0” if not

evaluate('REGEXP_INSTR(%1, ''^[0-9].*[A-z]+$'')',"User Details"."Primary Identifier")

Example 4: Get field which starts with numeric characters and ends with specific alphabetic character ‘A’.  This will return “1” if the string starts with numbers and ends with ‘A’ and 0 if not

evaluate('REGEXP_INSTR(%1, ''^[0-9].*A+$'')',"User Details"."Primary Identifier")

Example 5: Get a fields which starts with specific string ‘001’ and ends with specific string ’65’. This will return “1” if the string starts with ‘001’ and ends with ’65’ and “0” if not

evaluate('REGEXP_INSTR(%1, ''^001.*65+$'')',"User Details"."Primary Identifier")

Example 6: Get field which starts with alphabetic characters and ends with numeric characters.  This will return “1” if the string starts with alphabetic characters and ends with numbers and “0” if not

evaluate('REGEXP_INSTR(%1, ''^[A-z].*[0-9]+$'')',"User Details"."Primary Identifier")

Example 7: Get field which starts with alphabetic characters and ends with specific numeric characters ‘613’. This will return “1” if the string starts with alphabetic characters and ends with 613 and “0” if not (you can then filter on this for = 1)

evaluate('REGEXP_INSTR(%1, ''^[A-z].*613+$'')',"User Details"."Primary Identifier")

Example 8: Get all fields Public Note which start with “DISC” and has to be case “DISC” in capitals as written in the expression because of the “c” in the last parameter in parentheses.  The “c” means “case sensitive”.   “1” if DISC and “0” if not DISC

evaluate('regexp_instr(%1, ''^DISC+'', 1, 1, 0, ''c'')', "Physical Item Details"."Public Note")

Example 9: Get all fields Public Note which start with “DISC” and can be uppercase or lowercase or any combination (not case sensitive) because of the “i” in the last parameter in parentheses.  The “i” means “not case sensitive”.    “1” if DISC in any case and “0” if not DISC in any case.

evaluate('regexp_instr(%1, ''^Disc+'', 1, 1, 0, ''i'')', "Physical Item Details"."Public Note")

Example 10: Get all fields Public Note which have the string “DVD” in any position.  It must be uppercase (case sensitive) because the “c” in the last parameter in the parentheses means “case sensitive”.  The value given will be the location of the string DVD within the public note.  For example if the string DVD starts in position 61 then the value will be 61.  If there is no string DVD then the value will be 0.  To get all cases of the public note field with string DVD filter by “is greater than 0”.

evaluate('regexp_instr(%1, ''DVD+'', 1, 1, 0, ''c'')', "Physical Item Details"."Public Note")

Example 11: Change everything after the @ in the preferred email to “yilis.edu”.

SUBSTRING("Preferred Contact Information"."Preferred Email" FROM  1 FOR LOCATE('@', "Preferred Contact Information"."Preferred Email") ) || 'yilis.edu'

Example 12:  all course codes which have 4 numbers then ten digits then anything = 1 (note that this uses a substring of the course code)

evaluate('REGEXP_INSTR(%1, ''[a-zA-Z]{4}d{10}$'')',SUBSTRING("Courses"."Course Code" FROM 1 FOR 14)) 

Example 13: all course codes which have 4 numbers then five digits then anything = 1 (note that this uses a substring of the course code)

evaluate('REGEXP_INSTR(%1, ''[a-zA-Z]{4}d{5}$'')',SUBSTRING("Courses"."Course Code" FROM 1 FOR 9)) 

Example 14: all course codes which have 4 numbers then nine digits then anything = 1 (note that this uses a substring of the course code)

evaluate('REGEXP_INSTR(%1, ''[a-zA-Z]{4}d{9}$'')',SUBSTRING("Courses"."Course Code" FROM 1 FOR 13)) 

Example 15: all course codes which have 4 numbers then six digits and then nothing = 1

evaluate('REGEXP_INSTR(%1, ''[a-zA-Z]{4}d{6}$'')',"Courses"."Course Code")

Example 16: all course codes which have 4 numbers then six digits then anything = 1 (note that this uses a substring of the course code)

evaluate('REGEXP_INSTR(%1, ''[a-zA-Z]{4}d{6}$'')',SUBSTRING("Courses"."Course Code" FROM 1 FOR 10)) 

Example 17: all course codes which have 4 numbers then ten digits then anything = 1 (note that this uses a substring of the course code)

evaluate('REGEXP_INSTR(%1, ''[a-zA-Z]{4}d{10}$'')',SUBSTRING("Courses"."Course Code" FROM 1 FOR 14)) 

Example 18: all titles which have one word = 0.  This expression checks for the number of spaces in the title.  When there are no spaces then there is one word.  Change “Bibliographic Details”.”Title” to different field to check on different field.

EVALUATE('regexp_count(%1,%2)',REPLACE("Bibliographic Details"."Title", ' /',''), ' ')

– – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – – — – – – – – – – – – – – – – – – – – – – – – – – – – –

Using the regular expression:

Here we will do an example using example 5 to get all Primary Identifiers beginning with “001” and ending with “65”.  In a real report of course more fields would also be included, here we are only showing how the regular expression with a filter works.

ONE

Do “edit formula” on the Primary Identifier

TWO

See the default value for Primary Identifier

THREE

Change the default value to the regular expression

evaluate('REGEXP_INSTR(%1, ''^001.*65+$'')',"User Details"."Primary Identifier")

If desired also change the column header

FOUR

Filter the field

FIVE

Start by filter “=1”

SIX

See that all results start with “001” and end with “65”

SEVEN

Now do the filter to be “=0”

EIGHT

See that none of the results start with “001” and end with “65”