I have a meta key _student_registration_data stored in wordpress database having meta value as a serialized array like below:
a:9:{s:4:"name";s:20:"John Doe";s:11:"father_name";s:10:"Arnold";s:4:"cnic";s:13:"5540545612812";s:12:"home_address";s:9:"Scheme-33";s:16:"telephone_number";s:12:"923332654324";s:15:"registration_id";s:6:"08CS68";s:10:"program_id";s:1:"1";s:9:"cohort_id";s:1:"6";s:10:"section_id";s:1:"7";}
Now I want a mysql query or some builtin wordpress function with the help of which I can filter usermeta row having registration_id equal to 08CS68.
Thanks.
Nick's answer was the key, but it didn't solve my problem. Here's what actually worked:
SELECT * FROM (SELECT * FROM `cn_usermeta` WHERE meta_key = '_student_registration_data') as META WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(META.meta_value, 'registration_id', -1), ';', 2), ':', -1) = '"08CS68"'
Special thanks to Nick :)