MySQL Select only a part of the column


How can i select only a part of a column ? I know I can use substring, but i need to select a string part of a column. As an example: Column can contain the following:


I need to select only the DIRL part in one column, and the 100 part as another.

I could do it with this specific column like so:

SELECT SUBSTRING(column, 5) AS part1, SUBSTRING(column, 1, 4) AS part2 ....

But i cannot be sure that its always 4 letters (DIRL) before it gets numeric .. Can i somehow use REGEXP or something to extract only the numeric part and the letter part in each column ?

In other words.. Can i split a column by where the letters end. It could as an example contain DIRL100 or AB100200 which should be split into two columns each containing the letters from the column (DIRL or AB) and the digits from the column (100 or 100200) ?

Try this request:

SELECT LEFT(column, patindex('%[0-9]%', column)-1) AS Part1
     , RIGHT(column, LEN(column) - patindex('%[0-9]%', column)+1) AS Part2