Finding characters in a string and extracting the characters plus 4 more chars

We have a table that contains the Subject line of every email in our distribution list. I'm trying to extract our reference number and put it in another column. For now, our ref# starts with an 876 or 877 and it's 7 digits long. I used a CASE-WHEN-LIKE to identify the lines that contain 876 or 877 and minimize the lines I have to search through. Now I have to grab the 876 or 877 plus the next 4 characters on those lines. I tried using different RegEx statements with Replace Text without success. If you can point me in the right direction, that'd be great. Thanks!

Tagged:

Best Answer

  • MarkSnodgrass
    Answer ✓

    If you are doing this in a beast mode, you can use INSTR and SUBSTRING to get it. It would like like this

    CASE WHEN INSTR(`column`,'876') > 0 THEN

    SUBSTRING(`column`,INSTR(`column`,'876'),7)

     WHEN INSTR(`column`,'877') > 0 THEN

    SUBSTRING(`column`,INSTR(`column`,'877'),7)

    END

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • Hi @hilsmith

    What does your regex look like?

    you can try something like this

    ’’’

    regexp_replace(`subject`, ‘^.*(87[67]\d{4}).*$’,’\1’)

    ’’’


    I’m on mobile so sorry for the bad formatting

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MarkSnodgrass
    Answer ✓

    If you are doing this in a beast mode, you can use INSTR and SUBSTRING to get it. It would like like this

    CASE WHEN INSTR(`column`,'876') > 0 THEN

    SUBSTRING(`column`,INSTR(`column`,'876'),7)

     WHEN INSTR(`column`,'877') > 0 THEN

    SUBSTRING(`column`,INSTR(`column`,'877'),7)

    END

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Thank you for your answers! I ended up doing everything in the ETL. I separated the formulas MarkSnodgrass gave me since there are several ref# in a Subject line:

    Column name Find876 === SUBSTRING(`Subject`,INSTR(`Subject`,'876'),7)

    Column name Find877 === SUBSTRING(`Subject`,INSTR(`Subject`,'877'),7)

    I used a Text Formatting action to change the results to only show Numbers since the above wasn't just giving me a string with 7 numbers. Then, I used another Formula action to make sure I was only using the value with a length of 7. Not sure if I can combine the 2 formulas but this seemed to work.

    CASE WHEN LENGTH(`Find876`) = 7 THEN `Find876` ELSE 0 END

    CASE WHEN LENGTH(`Find877`) = 7 THEN `Find877` ELSE 0 END

    Thank you again!! 👍️

  • Just a note you can utilize the regular expression I gave in an ETL using the replace text tile. You just need to make sure you select Use Regex from the gear menu on the search text field (middle one)

    Also for clarification [67] says any single character in the list to match to

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • @GrantSmith, thanks for replying! When I was running a preview for the Replace Text I wasn't seeing any results. But as I said, I'm really new at this so maybe I'm missing a step. I was using the below:

    //domohelp.domo.com/hc/en-us/articles/360042923494-Pulling-a-Segment-of-Text-from-a-Text-String

    and another page that contains helpful RegEx statements (it included .* with the needed substring) and still nothing.

    Thanks!