Splitting by delimiter vs LOCATE() function equivalent

NateBI
NateBI Member
in Magic ETL

Hi All,

I want to fill the nulls in column A with the values in column B (or C if B is null).

A case statement did not work here so I am aiming to achieve this by combining the columns and then separating them by delimiter. Unfortunately, by separating them we are - of course- back to square one.

image.png

I was able to use this method for dates because I could do a LEFT(Date_shared,10) and extract the first date in the merge. However, for IDs, the first ID could be a length between 4 and 14.

It's a simple LEFT(FIND()) or LOCATE() sql situation but there doesn't seem to be a 'find position' function in the Magic ETL

All help appreciated

Answers

  • ST_-Superman-_
    ST_-Superman-_ Coach

    INSTR() will return the location of the first instance of a string


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • NateBI
    NateBI Member
    edited September 2022

    Hi @ST_-Superman-_ Thank you - I thought this was a Beast Mode only function. Plus SUBSTRING for the reverse - using position to extract from a string. Thanks!

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    use COALESCE() function

    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"
  • NateBI
    NateBI Member
    edited September 2022

    @jaeW_at_Onyx thank you. This would have been a real time saver 🙃

Categories