I am trying to split a field by delimiter in LookML. This field either follows the format of:

1. Managers (AE)
2. Managers (AE - MM)

I was able to split to first case using this

sql: case
      when rlike (${user_role_name}, '^.*[\\(\\)].*$') then split_part(${user_role_name}, ' ', -1)

However, I haven't been able to get the 2nd case to do the same. It's in a case statement so I am going to add another when statement, but am not able to figure out the regex for parentheses that contains spaces.

Thanks in advance for the help!

CodePudding user response：

By "split" the string, I think you mean you want to extract the part in parentheses, right?

I would do this using a regex substring method. You didn't mention what warehouse you're using, and the syntax will vary a little, but on snowflake that would look like:

regexp_substr(${user_role_name}, '\\([^)]*\\)')

So, for example, with the inputs you gave:

select regexp_substr('Managers (AE)', '\\([^)]*\\)')
union all
select regexp_substr('Managers (AE - MM)', '\\([^)]*\\)')