SQL in ETL

damen
damen Contributor
in Magic ETL

Hi All,

I'm currently trying to recreate a MySQL flow in Domo to ETL.

One of the first things we are doing in a table is taking out extra characters from a column to create a new trade_id

Here is how we are doing it.

image.png

when I go to add the formula tile and copy and paste this syntax, ETL is saying it doesnt recognize the 'LOCATE' function.

So keeping the same framework of finding the '%G2%' (for example) and taking everything from the left of those characters we have designated, is there a similar function that ETL will recognize? If so, what would one of the lines look like?

TIA

If this helps, feel free to agree, accept or awesome it!

Tagged:

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    I use the INSTR() function for this type of work.

    INSTR(STR,SUBSTR)Returns the position of the first occurrence of substring substr in string str.

    **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.
  • damen
    damen Contributor
    edited March 2023

    Ok so I went in and did this

    image.png

    and got this

    image.png

    but am looking for this

    image.png

    It feels like the error is in my syntax.

    @MarkSnodgrass

    If this helps, feel free to agree, accept or awesome it!

  • MarkSnodgrass
    MarkSnodgrass Coach

    Unfortunately, it looks like INSTR and LOCATE have their arguments reversed. For INSTR() it needs to be INSTR(trailid,'G2'), for example.

    **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.
  • damen
    damen Contributor

    Sheesh. Thanks @MarkSnodgrass!!

    If this helps, feel free to agree, accept or awesome it!

Categories