7 Replies Latest reply: Jan 18, 2015 7:14 AM by anbu cheliyan RSS

    Match string to substring from another table

      Hi everyone,

       

      Imagine two simple tables - one with IDs and the other with urls. Each url contains one ID, the ID position in the url is not fixed, and urls might also contain other non-ID numbers as the last example in Table2. Simple Left(), SubField(), KeepChar() won't work.

       

      Table1

      idcategory
      1111a
      2222b
      3333c
      4444d
      5555e

       

      Table2

      urls
      http://www.ipsum.com/1111/lorem
      http://www.ipsum.com/lorem/2222
      http://www.lorem.com/3333/lorem
      http://www.lorem.com/ipsum/lorem/4444

      /231/lorem/5555/lorem

       

      I tried to extract the IDs from the urls with If(IsNum(url_sub) and Len(url_sub) = 4, url_sub, Null()) which somehow works as an expression but not in the script. Probably Mapping and MapSubString() is the best option but I couldn't make it work either. Do you have any ideas?

       

      Thanks,

      Ivelin