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

hilsmith
hilsmith βšͺ️

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
    MarkSnodgrass Portland, Oregon 🟀
    Accepted 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

Answers

  • GrantSmith
    GrantSmith Indiana 🟀

    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

  • hilsmith
    hilsmith βšͺ️

    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!! πŸ‘οΈ

  • GrantSmith
    GrantSmith Indiana 🟀

    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

  • hilsmith
    hilsmith βšͺ️

    @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!

Sign In or Register to comment.