Alma Analytics – How to use a regular expression to display the numerical value if after the numerical value the string pages or leaves appears
by Yoel KortickNote that if you do copy/paste here make sure the quotation marks transfer correctly.
Question
How can I display the numerical page numbers from the field “Bibliographic Details”.”Local Param 03″ which appear before the string “pages” or “leaves” or “unnumbered pages” or “unnumbered leaves”?
If the string “pages” or “leaves” does not appear then I do not want anything to appear.
Here are some examples of the original field and what I want the regular expression to do:
| “Bibliographic Details”.”Local Param 03″ | Desired Regular expression result |
| xviii, 229 pages : illustrations ; 23 cm | 229 |
| 432 pages : illustrations ; 25 cm | 432 |
| xiii, 218 pages, 19 leaves of plates : illustrations ; 22 cm | 218 |
| 26 leaves : illustrations ; 28 cm + 1 audio disc (approximately 33 min. : digital ; 4 3/4 in.) | 26 |
| 4 unnumbered pages ; 28 cm | 4 |
| 24 unnumbered leaves : chiefly illustrations ; 44 x 28 cm | 24 |
| 1 videodisc (85 min.) : sound, color ; 4 3/4 in. | It should be empty |
Answer
Here is the regular expression
CASE WHEN "Bibliographic Details"."Local Param 03" like '%pages%' or "Bibliographic Details"."Local Param 03" like '%leaves%' THEN evaluate ('REGEXP_REPLACE (%1, ''[^0-9]'')',substring("Bibliographic Details"."Local Param 03" from CAST(evaluate('REGEXP_INSTR(%1, ''*[0-9].*'')',"Bibliographic Details"."Local Param 03") AS INT) for 5)) ENDHere we have edited a field and added the regular expression to the column formula
In the report, in addition to the field with the regular expression, we also have the MMS ID and the original field “Bibliographic Details”.”Local Param 03″ (so we can compare and see the results)
Here are the results. The appear exactly as desired in the examples in the table in the above question.
2 Replies to “Alma Analytics – How to use a regular expression to display the numerical value if after the numerical value the string pages or leaves appears”
Leave a Reply
You must be logged in to post a comment.
Is this only possible on local param fields? I’m trying to do something similar to separate subfields (a and g) of the Bib Details Author field and getting an error.
CASE
WHEN
“Bibliographic Details”.”Author” like ‘%author%’
THEN
evaluate (‘REGEXP_REPLACE (%1, ”[^0-9]”)’,substring(“Bibliographic Details”.”Author” from CAST(evaluate(‘REGEXP_INSTR(%1, ”*[0-9].*”)’,”Bibliographic Details”.”Author” AS INT) for 5))
END
Formula syntax is invalid.
[nQSError: 10058] A general error has occurred. Please have your service administrator review this error. (HY000)
[nQSError: 43275] Message returned from OBIS [ecid:35a44f5e-12b3-4557-871f-b0286768d4e6-004c16c5,0:1:20 ts:2025-03-26T17:58:13.482+00:00]. Please have your service administrator review this error. (HY000)
[nQSError: 27002] Near : Syntax error Please have your service administrator review this error. (HY000)
[nQSError: 26012] . Please have your service administrator review this error. (HY000)
SQL Issued: SELECT CASE WHEN “Bibliographic Details”.”Author” like ‘%author%’ THEN evaluate (‘REGEXP_REPLACE (%1, ”[^0-9]”)’,substring(“Bibliographic Details”.”Author” from CAST(evaluate(‘REGEXP_INSTR(%1, ”*[0-9].*”)’,”Bibliographic Details”.”Author” AS INT) for 5)) END FROM “Digital Inventory”
Hello Libaccess Kettering:
1. The formula works on all fields and not only local param fields.
2. Your error in the formula may be the result of copying and pasting the text from the blog here and having quotation marks or other characters get ruined in the copy paste. That happens sometimes and some characters need to be retyped. It depends on windows and other settings.
3. As explained by email I have put a working copy in your Al;ma Analytics and told you where it is
4. Here is working formula which does what you tried to do on field “Bibliographic Details”.”Author”
CASE
WHEN
“Bibliographic Details”.”Author” like ‘%Author%’ or “Bibliographic Details”.”Author” like ‘%author%’
THEN
evaluate (‘REGEXP_REPLACE (%1, ”[^0-9]”)’,substring(“Bibliographic Details”.”Author” from CAST(evaluate(‘REGEXP_INSTR(%1, ”*[0-9].*”)’,”Bibliographic Details”.”Author”) AS INT) for 5))
END