the string is like this

a:3:{s:8:"ref_type";s:4:"user";s:3:"tid";s:18:"TXID17073204182661";s:7:"message";s:19:"3757000431691375718";}

I want to get only the text between the last 2 ", which is 3757000431691375718

I have tried REGEXP_SUBSTR(wml.data, '[^:]+$') but this will return "3757000431691375718";}
Anyone can help?
Thank you

>Solution :

You might use this pattern :

SELECT *, REGEXP_SUBSTR(data, '(?<=\")[^"]+(?="[^"]*$)')
FROM mytable

The pattern matches:

(?<=\") Assert a " to the left
[^"]+ Match 1+ occurrences of any char except "
(?="[^"]*$) Assert a " to the right, followed by any char except "

Demo here