Take value from text: random value position

[evgeniystuchalk]

Hi! I have table with calls. All information about call keeps in text cell, like

{"PHONE":"84955438307","DURATION":"57","SRC":"","UNIQ":"1448962343"}

I need to take duration as a number. How I can extract it from text?

I can predict position of DURATION, because most of calls have this kind of data in text cell:

{"UNIQ":"62c871ef-c0de-4d76-af2d-d1f9413cceaf","LINK":"https://rcsgroup.onpbx.ru/download_amocrm/eyJ1IjoiNjJjODcxZWYtYzBkZS00ZDc2LWFmMmQtZDFmOTQxM2NjZWFmIi...","PHONE":"74950550244","DURATION":41,"from":"+74950550244","extinfo":"","SRC":"teslatelecom"}

[JonnyPoole]

This expression will :

- take the text between the first and second commas

- from that text, remove the double quote characters

- of the remaining text, take the characters after the comma

mid(

  purgechar( mid(Call, index(Call,',',1)+1, index(Call,',',2) - index(Call,',',1) - 1), '"'),

    index( purgechar( mid(Call, index(Call,',',1)+1, index(Call,',',2) - index(Call,',',1) - 1), '"'),':')+1

)

You could use it on a load script as well. Read the assumptions above carefully and employ only if these assumptions are correct with regards to the possible data values. It may be better to tweak the expression to be more resilient... but again that depends on the possible data values.