Extract part of URL string in a table report?

Looker Q&A Looker
1

I have a URL string in a Looker table report where I want to extract part of the URL to display as a separate column. The URL string is not a fixed format, so I can’t use a known start and finish position

I can see a “split” function but there does not appear to be a related function to retrieve a list item by position.

In my report I have had to create multiple table calculations to represent different parsing steps. Is there an easier way of doing this?

2

Would you provide some examples of the URL strings? And what database are you using?

3

Hi,
What part of the URL are you trying to extract?
I’ve always found regular expressions (in LookML) very helpful for things like this but if you’re looking to do this in a table function, you’ll probably need to use:

replace(substring(${string},if(position(${string},"://")=0,1,position(${string},"://")+3),len(${string})),"www.","")

That’ll give you the domain without the usual gumf at the beginning. You can then trim-off anything after the slash:

if(position(replace(substring(${string},if(position(${string},"://")=0,1,position(${string},"://")+3),len(${string})),"www.",""),"/")=0,replace(substring(${string},if(position(${string},"://")=0,1,position(${string},"://")+3),len(${string})),"www.",""),substring(replace(substring(${string},if(position(${string},"://")=0,1,position(${string},"://")+3),len(${string})),"www.",""),1,position(replace(substring(${string},if(position(${string},"://")=0,1,position(${string},"://")+3),len(${string})),"www.",""),"/")-1)

It’s messy, but I hope it helps!
(Revised double quote characters)

4

Thanks for the input, but that seems like a lot of effort for a straightforward parsing operation.

The function is missing some obvious functions. Like:

  • list_count, = return number of items in a list
  • list_item = return the nth item ina list
  • left = extract left most text chars
  • right = extract rightmost text charts
  • substring = with a start and finish range for replacing
1 Like
5

Which is why I would suggest using a regular expression in the LookML.
To help the community help you, please could you explain a little more about what you’re trying to parse from the URL?

Many thanks.