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

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!

MarkSnodgrass

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

GrantSmith

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

MarkSnodgrass

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

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

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

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