How to get substring dynamically

How can I get substring dynamically, I want to split one word into two parts one is a string and other to no,

AA112 this is my string so from sql query

SELECT substr(thisColumn, 1,2) AS String from thisTable

I am getting AA in the query the index(2) I am pasing is dynamic I am getting from some value, So here I am able to get the string now I want to get the number +1 like it is 112 then -> 113

I have tried several queries but it is not working, I have tried doing it with javascript substring but it didn't work out.

Sample output
AAA123 -> AAA and 124

AA4 -> AA and 5

BBBB8 -> BBBB and 9

<!-- begin snippet: js hide: false console: true babel: false -->
<!-- language: lang-js -->

const input="AA112";
var numberPattern = /\d+/g;
const number = input.match(numberPattern)[0];
const text = input.replace(number,"");

console.log(text);   //AA
console.log(number); //112

const incremented = parseInt(number) + 1;
console.log(incremented);

<!-- end snippet -->

Using LOCATE, IF,LEAST and SUBSTRING over two sub-query can achieve the output result you're looking for.

SELECT VAL, 
       SUBSTRING(VAL,1,FIRSTNUM-1),
       SUBSTRING(VAL,FIRSTNUM,999)+1 
FROM
(SELECT VAL,LEAST(V0+0,V1+0,V2+0,V3+0,V4+0,V5+0,V6+0,V7+0,V8+0,V9+0) FIRSTNUM FROM
(SELECT VAL,
       IF(LOCATE(0,VAL)=0,999,LOCATE(0,VAL)) V0,
       IF(LOCATE(1,VAL)=0,999,LOCATE(1,VAL)) V1,
       IF(LOCATE(2,VAL)=0,999,LOCATE(2,VAL)) V2,
       IF(LOCATE(3,VAL)=0,999,LOCATE(3,VAL)) V3,
       IF(LOCATE(4,VAL)=0,999,LOCATE(4,VAL)) V4,
       IF(LOCATE(5,VAL)=0,999,LOCATE(5,VAL)) V5,
       IF(LOCATE(6,VAL)=0,999,LOCATE(6,VAL)) V6,
       IF(LOCATE(7,VAL)=0,999,LOCATE(7,VAL)) V7,
       IF(LOCATE(8,VAL)=0,999,LOCATE(8,VAL)) V8,
       IF(LOCATE(9,VAL)=0,999,LOCATE(9,VAL)) V9 FROM mytable) A ) B;

Here I use LOCATE to find the location of the number occurrence in the value then REPLACE to change the value to 999 if it returns 0. This is essential for the next operation using LEAST because if I leave it 0, it'll return all 0 instead. The reason I use LEAST is because if you look at the 4th data example in the fiddle it's AAAAA321. This will return the result in grid like the following:

+----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+
|   VAL    | V0  | V1  | V2  | V3  | V4  | V5  | V6  | V7  | V8  | V9  |
+----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+
| AAA123   | 999 |   4 |   5 |   6 | 999 | 999 | 999 | 999 | 999 | 999 |
| AA4      | 999 | 999 | 999 | 999 |   3 | 999 | 999 | 999 | 999 | 999 |
| BBBB8    | 999 | 999 | 999 | 999 | 999 | 999 | 999 | 999 |   5 | 999 |
| AAAAA321 | 999 |   8 |   7 |   6 | 999 | 999 | 999 | 999 | 999 | 999 | <---this
| AAA45    | 999 | 999 | 999 | 999 |   4 |   5 | 999 | 999 | 999 | 999 |
+----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+

The first number occurrence is actually '3' but because I construct the query running from 0-9, I'll always get the first number in front (in this case '1' is the 8th occurrence) despite it being on the middle or last position. That's why LEAST will correctly take which number is actually located first using LOCATE function.

In the last outer query, I use the result from the LEAST function (assigned as FIRSTNUM) as the defining value for the SUBSTRING. The first substring I use it as the end location subtract by 1 to get the strings and in the second substring I use it as the first location to get the number (I end the location with 999).

Fiddle: https://www.db-fiddle.com/f/F15cxiYJxGcozYUbQukeB/5

Edit: I realized I cannot use REPLACE because if the location is like 10, it will replace 0 to 999 which makes it become 1999 instead. So I use IF.