What is the best way to parse URLs?

I currently am bringing in data from google ads and one of the columns is `landing page url`.


I am trying to parse this row from:

https://m.ufhealth.org/uf-health-pregnancy-care?utm_source=google&utm_medium=tj%20ppc&utm_campaign=fy21%20ob%20services&gclid=EAIaIQobChMIo7Hl4pCq8gIV_21vBB1iYQ_JEAAYASAAEgJD3fD_BwE

to uf health pregnancy care only.


I am starting to learn expression but do not have all the knowledge yet to do this.

Tagged:

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷
    Answer ✓

    I would suggest using the split_part function to extract this.

    split_part(
    split_part(`Profile Picture URL`,'/',4)
     ,'?',1)
    

    The innermost split_part will get the information after the slash that is after the base domain name. The outer split_part will then extract everything before the question mark.

    Hope this helps.

    **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

  • GrantSmith
    GrantSmith Indiana 🥷
    edited August 24

    Here's a REGEX version:

    Something like this might work for you in a formula tile in Magic ETL 2.0:

    REPLACE(REGEXP_REPLACE(`landing page url`, '^.*ufhealth\.org\/([^?]+).*$', '$1'), '-', ' ')
    

    It's looking for your base URL and then pulling everything after it until it finds a ? character and ignoring everything before and after and then replacing it with just your page.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • @MarkSnodgrass - This is one of those times that I wish Domo's documentation kept up with Domo's updates. There is no SPLIT_PART() function listed in the beastmode function list, but it definitely works! I was just able to clean up some really nasty substring() beastmodes :)


    ______________________________________________________________________________________________
    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
    ______________________________________________________________________________________________
  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    @ST_-Superman-_ LOL! I'm glad it helped you do some cleanup. I agree with you on the documentation. This function and others show up in the formula tile in Magic ETL, but not when you scroll through the list when you are creating a beast mode in Analyzer. Scroll through the list when you are in Magic ETL and you will see quite a few more that you can use. I've only found a couple that didn't also work in Analyzer.

    **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.
  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🔴
    edited August 30

    @zcameron "This is one of those times that I wish Domo's documentation kept up with Domo's updates. There is no SPLIT_PART() function listed in the beastmode function list"


    @MarkSnodgrass and @ST_-Superman-_

    while Domo does try to maintain parity between what we can do in formula tiles and what we can do in Beast Modes, it's important to keep in mind that Magic ETL is not executing in Adrenaline (whereas beast modes are).

    All the formula tile formulas borrow from MySQL / Adrenaline formula syntax, but they are reduced/interpreted into Java (i believe).

    Usually if there's a specific function missing it's an easy ask to toss it over to support / product feedback ... whether it'll actually get implemented might be a different question :P

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🔴
    edited August 30

    blank post for the points :P sorry can't delete it.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"