Urgent : how do i clean the data in this format

[n1ef5ng1]

I have this set of column where their data are display as :

{'id': 20921262, 'value': 'call'}

{'id': 20921262, 'value': 'feedback_tab'}

I just want the value for call , feedback_tab for the two example above. Meaning is after 'value' :

How can i code it to achieve the output

[adamdavi3s]

What you have there is an Array (sorry not JSON) there should be a good number of threads which can help load this.

WHat is the raw data source as it might be easier to load from there properly rather than parsing a string

[n1ef5ng1]

is actually retrieving by web scrapping using python. that is the best output that we can work from now though...

[adamdavi3s]

Ok in that case this code will work for this very specific example!

purgechar(TextBetween(putyourfieldnamehere,'value'&chr(39)&':','}'),chr(39)) as test;

[adamdavi3s · Accepted solution]

Actually this is a little cleaner and more secure:

TextBetween(putyourfieldnamehere,'value'&chr(39)&': '&chr(39),chr(39)&'}') as test;

[n1ef5ng1]

wow thanks. it works. initially i use mid, diden know about this text between. thanks!

[adamdavi3s]

No worries at all, its a very handy feature for parsing text!