String contains key:value pairs. How to extract value for user-provided key ?

Backend: Redshift table; the column C (varchar (1000) ) has data like this:
A1:B1&A4:B7&A9:B12
It is basically the key:value pairs. The & separates the pairs, the : separates the key and value.
The number and length of keys and values is not fixed. The content of keys and values is not known in advance.
The contents of keys and values are not overlapped.
Goal:
Looker user enters the key value in UI box; Looker returns back the records with this key, do no display the entire C column,
but display only the value from C column which corresponds to the user-provided key .

I tried to create the new Custom dimension:

substring( ${T.C}  ,  
position( ${T.C},  "user_key:" ) + length("user_key:") + 1 ,  length_of_value )

but in expression above I do not know how to calculate the length_of_value; 2 cases need to be covered: the value in the middle of string or value at the end of string.

0 0 728
0 REPLIES 0
Top Labels in this Space
Top Solution Authors