How can I split a string at a certain character with a Table Calculation or Custom Field? (split part a field by delimiter)

[sam8]

Knowledge Drop

Last tested: Jun 1, 2020
 

You can split a value by a delimiter in Table Calculations and Custom Fields* by using the substring and position functions.

In this example:

We get the first position of the character b in the name string:

position(${name},"b")

Then we get everything before that position:

substring(${name},0,position(${name},"b") -1 )

Alternatively, you could get everything after that position:

substring(${name},position(${name},"b") +1 , length(${name}))

*When using Custom Fields, the looker substring() expression is converted into the Looker generated sql, and most dialects do not allow negative integers for their Substring/SUBSTR/Position functions. So you'll want to wrap this expression in a conditional if() statement to make sure you address the scenario where the search string doesn't exist

When using in Custom Field (Custom Dimension)

if(position(${name},"b")=0,${name},substring(${name},0,position(${name},"b") -1 ))

This content is subject to limited support.                

[Ilya1]

this is awesome, custom dimensions are important to roll up to a specific granularity (table calc will require more granular values than needed). 

thanks!