1 Reply Latest reply: Jan 5, 2016 5:05 AM by Manuel Carmona RSS

    Finding and extracting a substring

    Manuel Carmona

      Hi there.

       

      I know that the wildmatch function allows to detect if a particular substring is inside a certain string but, would there be any way to extract that substring?

       

      The string would be a text from an email subject field which sometimes includes (in a variable location) a reference number that always starts with Q2 and has a length of 10 characters (e.g. Q238832439). I'd need to get that 'Q238832439' extracted.

       

      Any help or guidance will be appreciated.

       

      Thanks.

      M.

        • Re: Finding and extracting a substring
          Manuel Carmona

          Hi again.

           

          Found something that seems to work:

           

              if(wildmatch(text_field,'*Q2*')=0,'NO','Q2'&left(subfield(text_field,'Q2',2),8)) as my_field;

           

          It works as follows:

           

          if the text field contains one of the references we are looking for it takes the part of the string to the right of 'Q2', then extracts 8 characters starting from the left and adds 'Q2' at the beginning.

           

          Otherwise it includes the word 'NO' in my_field.

           

          So kind of sorted.