0

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 !

Emad Rashad Muhammed
  • 75
  • 1
  • 2
  • 11
  • If the format is always the same (1 character followed by the number you want), then you could use the sql `substring()` function – IGP Feb 28 '22 at 15:54
  • am afraid it may be one character or two like A101 , B101 .... AA101 , AB101 etc – Emad Rashad Muhammed Feb 28 '22 at 16:20
  • @RaymondNijland you know what , it actually did the trick. thanks can u post it as an answer so that i can accept it. thank you so much – Emad Rashad Muhammed Feb 28 '22 at 18:29
  • @RaymondNijland hey man just one note the way you suggested works great with all numbers except numbers that ends with 0 or 00 so if the number is A10 it will be stored as 1 if it was A100 it will also stored as 1 if it was A2500 it will be stored as 25 .... and so on so what do you suggest !!! – Emad Rashad Muhammed Mar 09 '22 at 07:44
  • the issue is in this part REVERSE(REVERSE("A100") << 0) – Emad Rashad Muhammed Mar 09 '22 at 07:48
  • forget the `REVERSE(REVERSE("A100") << 0)` that indeed does not work with 0 at the end i forgot about that case does this even more tricky SQL http://sqlfiddle.com/#!9/c998e1/12 help you out, it seams to be working fine with test data – Raymond Nijland Mar 09 '22 at 08:28
  • @RaymondNijland again you are the savior :-D do you know how can i perform this inside a laravel migration to make my virtual column as i can see on sqlfiddle a large select statement with many unions !! – Emad Rashad Muhammed Mar 09 '22 at 08:34
  • i think i would make a view out of it instead of a virtual column it makes it bit more sense and query the view instead in your application where would need that column information ..it also might make it more easy to use with laravel migration but i am not sure of it as i never used laravel migration – Raymond Nijland Mar 09 '22 at 08:40
  • @RaymondNijland ok man i really appreciate you help so thank you so much hope you're having a great day – Emad Rashad Muhammed Mar 09 '22 at 08:42

0 Answers0