MySQL Virtual Column Wrong Values

Question

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

**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 **

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
@aymcg31 thanks for pointing out to this i edited my question — Emad Rashad Muhammed, Mar 09 '22 at 08:18
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

score 0 · Answer 1 · answered Mar 09 '22 at 08:23

0

With mySQL8+ you can do this :

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

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

answered Mar 09 '22 at 08:23

aymcg31

321
1
7

am afraid i cant use Mysql8 version as we have 5.7.23 so this function will be unavailable – Emad Rashad Muhammed Mar 09 '22 at 08:27
Ok, then at this point you have to write a function that removes all non digits character. You'll find plenty online, some very efficient even. – aymcg31 Mar 09 '22 at 08:30

MySQL Virtual Column Wrong Values

1 Answers1