-1

i really loves mysql virtual generated columns but am having a small issue with it am trying to make a virtual column from a varchar column where i extract the number as the following.

DB::statement('ALTER TABLE reservations ADD number_vc  BIGINT AS (REVERSE(REVERSE(number) << 0)) ');

my issue is in this part REVERSE(REVERSE(number) << 0))

as if you tried now to run this part of sql in any sql editor as the following

SELECT REVERSE(REVERSE("A100") << 0)

it will generate the following

enter image description here

**What Am Trying To Achieve **

if the string was A100 after reverse i need 100 also the schema of the varchar not always one character it may be like this A100 , AA100 , AB100

**Edit 2 :Am Afraid i did go production a week a go and here is the result **

enter image description here

SOS
  • 6,430
  • 2
  • 11
  • 29
Emad Rashad Muhammed
  • 75
  • 1
  • 2
  • 11
  • 2
    No. ```REVERSE("A100") << 0``` produces "1" and ```REVERSE(1)``` produces 1. Nothing wrong here. You should say what you're trying to achieve and give sample datas so we understand better – aymcg31 Mar 09 '22 at 08:12
  • If you want to trim the leading "A" you can use SUBSTR: `SELECT SUBSTR("A100", 2);` – Vadim Sirbu Mar 09 '22 at 08:14
  • IF it's always using that format and always using only one letter than yes, what Vadim said should be enough EDIT: OK not always one letter then that's different – aymcg31 Mar 09 '22 at 08:18
  • @VadimSirbu no it is not always one character it may be 2 like A100 , AA100 , AB100 – Emad Rashad Muhammed Mar 09 '22 at 08:20
  • Does this answer your question? [How to get only Digits from String in mysql?](https://stackoverflow.com/questions/37268248/how-to-get-only-digits-from-string-in-mysql) – N69S Mar 09 '22 at 08:24
  • Refresh the page to see my answer – aymcg31 Mar 09 '22 at 08:25

1 Answers1

0

With mySQL8+ you can do this :

SELECT REGEXP_REPLACE('A100', '\D', '');

It's basically replacing non digits characters with nothing using a regexp.

aymcg31
  • 599
  • 3
  • 12