Select a substring

How do I select a substring from a string like this
This string represents pairs of names and values of custom fields
I want to separate one or more values into individual columns
Thanks in advance

Use a custom expression for each required column:

If it's something you'll be using a lot, add a new column to the view in the database. If you're using tables rather than views, now would be a good time to change - you'll thank me for this later!

Alternatively, add the query with custom expression to a Model. I'm not a big fan of models but they are improving.

Thanks but problem I have is that the data does not have uniform structure. The query targets a database of assets where different types of assets have unique sets of custom fields. The column is called "custom fields" but for a particular asset type the key value pairs could be different to other types or could be in a different sequence.

You need to clean up the data structure first by the sound of it. Can you create a view that splits out the different custom fields into their own fields?

I had what I think was a similar structure where one column could be used for anything from currency to character to boolean depending upon the value in another column. I just split that out into several more usable columns.

Sometimes you have to fix bad database design first!

Thanks for reply. It is a popular job management system using metabase to enable queries. Unfortunately this is the best dataset it will output with everything dumped into one string. I have now solved the problem externally using an excel macro to pull the string apart.