Tech Blog

How to use a regular expression to display part of a field based on characters before after or part of a string

There may be situations where you may want to display only part of a string based on characters which appear before,  after or part of a string.

Here we will show two examples using the “Network Number” and the “Subjects”, both of which are part of the sharded “Bibliographic Detai;ls” folder.

Example using the “Network Number”

For example the “Network Number” in the shared “Bibliographic Details” folder may contain several network numbers and we want to only display the first one which starts with “(Nz)” or “(nz)”:

We can also add a filter to the report to only include rows for which there is a Network Number which includes “(Nz)”.

Now all rows have “(Nz)” somewhere in the field:

If we want to display only the first one which starts with “(Nz)” or “(nz)” then we can use a regular expression and put it in the “edit formula” of the “Network Number”.

Here is a regular expression to do this.  This regular expression will display the part of the string in the Network Number field which starts with “(Nz)” or “(nz)” and it will display the string either up to a semicolon (not including the semicolon) or to the end of the field.

SUBSTRING(“Bibliographic Details”.”Network Number” FROM  LOCATE(‘(nz)‘, lower(“Bibliographic Details”.”Network Number”))

FOR

case LOCATE(‘;’, lower(“Bibliographic Details”.”Network Number”), LOCATE(‘(nz)‘, lower(“Bibliographic Details”.”Network Number”)) )

when 0 then 20 

else

LOCATE(‘;’, lower(“Bibliographic Details”.”Network Number”), LOCATE(‘(nz)‘, lower(“Bibliographic Details”.”Network Number”)) )

– LOCATE(‘(nz)‘, lower(“Bibliographic Details”.”Network Number”))

end

)

Here in the “Criteria” tab we are putting this expression in the “edit formula” of the field and then renaming the field to “(Nz) Network Number:

 

Now the results show only the Network Numbers that start with “(Nz)”:

Here is the “Before” and “After”:

BeforeAfter

 

Example using the “Subjects”

In addition to the Network Number, this can be done with any other field.

Here we have subjects that start with “Feminism”, and they appear together with other subjects:

If we want to display only the first subject which start with “Feminism” up to and not including the semicolon after the subject (to the end of the field if there is no semicolon at the end) then we can use the same syntax as above and

  1. Instead of “(Nz)” put “Feminism”
  2. Instead of “Bibliographic Details”.”Network Number” put “Bibliographic Details”.”Subjects”
  3. Instead of “20” put “100” (this is the maximum length of the field to display)

SUBSTRING(“Bibliographic Details”.”Subjects” FROM LOCATE(‘feminism’, lower(“Bibliographic Details”.”Subjects”))

FOR

case LOCATE(‘;’, lower(“Bibliographic Details”.”Subjects”), LOCATE(‘feminism’, lower(“Bibliographic Details”.”Subjects”)) )

when 0 then 100

else

LOCATE(‘;’, lower(“Bibliographic Details”.”Subjects”), LOCATE(‘feminism’, lower(“Bibliographic Details”.”Subjects”)) )

– LOCATE(‘feminism’, lower(“Bibliographic Details”.”Subjects”))

end

)

First edit formula for the subjects:

Put in the regular expression and change header as desired:

Now we have this:

Here is the “Before” and “After”:

BeforeAfter

 

 

 

 

Leave a Reply