Fun with string functions

Options
MattRK
MattRK Member
in Magic ETL

I do a lot of string functions to look at URLs. If I want to use a SUBSTRING and INSTR Function what's the best way to move the end a dynamic number of characters? 

EXAMPLE: I want to show the string after the 1st '=' and before 'the_end'  in the 'big_string' field.

I wrote: 
SUBSTRING(`big_string`,INSTR(`big_string`,'=')+1,INSTR(`big_string`,'the-end')-INSTR(`big_string`,'='))

The above works but I'm getting an extra character at the end that preceeds 'the-end' that's different. I tried sticking -1 in a few different places and it broke.  Thoughts?


Best Answer

  • Rich
    Rich Contributor
    Answer ✓

    Sure thing. So here is what I did to model your question:

     

    I created this string (my garbage example) as a URL model of what I think you're looking for:

     https://www.google.com?formula=yes&donewithstring=the-end

     

    I then used your code example to parse it, and I think this will work the way you want (added in red below):

     

    SUBSTRING(`big_string`,INSTR(`big_string`,'=')+1,(INSTR(`big_string`,'the-end')-1)-INSTR(`big_string`,'='))

Answers

  • Rich
    Rich Contributor

    MattRK - are you doing this string manipulation in a beastmode, Magic ETL, or SQL? My answer changes a bit depending on which environment you're in.

  • MattRK
    MattRK Member

    As a beast mode. In SQL it's less difficult. 

  • Rich
    Rich Contributor
    Answer ✓

    Sure thing. So here is what I did to model your question:

     

    I created this string (my garbage example) as a URL model of what I think you're looking for:

     https://www.google.com?formula=yes&donewithstring=the-end

     

    I then used your code example to parse it, and I think this will work the way you want (added in red below):

     

    SUBSTRING(`big_string`,INSTR(`big_string`,'=')+1,(INSTR(`big_string`,'the-end')-1)-INSTR(`big_string`,'='))

  • MattRK
    MattRK Member

    That works great, this is a good way to repilcate a "MID" function until such time as one exists in a Beastmode.

Categories