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

      Hello,

       

      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.