Question

Extracting characters from string

  • 12 February 2018
  • 2 replies
  • 1401 views

I want to add filter to my column such way that based on number selected in filter it will return number of character in output

eg


2 replies

Userlevel 3
Badge

Hey @Swapnil_Pimple seems like your scenario would be great for using LookML Parameters with SQL string functions!! Here is the doc on parameters in Looker.

I just did this as a calculated field, which was useful for ad-hoc work needed to then merge with another look. You can use the parameter method but that’s a more permanent build requiring you to update the lookml. 

 

In a calculated field you can use substring() and position() functions. 

 

substring($input,1,3) - this starts from position 1 and returns 3 characters. 

 

You can also use the position argument to start the substring from another part of the string. 

 

substring($input,position($input, ‘r’ -1),3) would find the position of ‘r’ and subtract 1 position from it, so it would start on the ‘e’ in ‘America’ and then return 3 characters giving you ‘eri’ 

 

Obviously this wouldn’t work for your example, but I used it in finding id numbers in a url which were always followed by .php. so substring($input, position($input, ‘.php’ -8), 8) gave me the 8 digit id number directly preceding the ‘.php’ in the url. :) 

 

Also, I realize this post is 3 years old, but I’ll need it again, so this comment is mostly for my own reference ;) 

Reply