8 Replies Latest reply on Jul 16, 2021 5:39 AM by Nico Heinze

    REG_EXTRACT data from second set of parentheses (if exists)

    Bobby Price Active Member



      I am splitting a location field into 2 fields (location and location code).


      The format of the field is something like USA (USA1); so USA -> location, USA1 -> location_code.


      For location I am using REG_REPLACE(in_Location, '\(.*?\)', '')


      And for location code I am using REG_EXTRACT(in_Location,'.+?\((.+?)\).*',1) to pull the data from the set of parentheses.


      This is working fine but during validation I noticed there are a few records that have an extra set of parentheses that belong in the location field. For example USA (New York) (USA1).


      I am stuck on how I can pull the data from the second set of parentheses for the location code when the first set isn't always there.


      Any suggestions are greatly appreciated.