am trying to use mysql virtual columns feature to achieve a search mechanism where i have booking numbers may be a real integer like 101 or an alpha numeric as A101 , B101 etc.
So i do need to make a search mechanism where the use will input 101 and i will fetch all bookings with 101 which certainly one booking but also i will fetch all those bookings with characters like A101 , B101 i hope you got the point.
i used virtual columns with json and they were very effective like the following.
DB::statement('ALTER TABLE finances ADD meta_type BIGINT AS (JSON_UNQUOTE(meta->"$.type"))');
and here am extracting the json value and assign it to my new virtual column
now i need to make the same step but with another logic
DB::statement('ALTER TABLE bookings ADD number_vc BIGINT AS ( convert number column which is alpha numeric to integer only )');
i want to get rid of all char in the number column value strip it all and leave only the integer to be assigned to my number_vc new column , so when my migration executed successfully i will end up with the following
| id | number | number_vc (the new virtual column) |
|---|---|---|
| 1 | 101 | 101 |
| 2 | A101 | 101 |
| 3 | B101 | 101 |
So am missing the As ( convert in my statement ) so any help would be appreciated !