Extracting alpha and numeric parts from a column

Question

I have a table tab1 with a column col1 that has compound alpha-then-numeric values, like this:

abc123
xy45
def6
z9

I need to extract the values as separate columns in a query, with the numeric part in a column of integer datatype.

If the two values had a consistent start and end positions, the job could be done with substring(), as you can see the start of the numeric part varies.

Is there an elegant way to tackle this, or must it be done with a series of unions of each possible start point using a regex match to separate the cases, or rolled up in a huge case statement?

Answer 1

Yes, it is:

SELECT
  @col:=col1 AS col, 
  @num:=REVERSE(CAST(REVERSE(@col) AS UNSIGNED)) AS num,
  SUBSTRING_INDEX(@col, @num, 1) AS word
FROM
  tab1

-will work only if your column contain letters and then numbers (like you've described). That's why double REVERSE() is needed (otherwise CAST() will have no effect). Check this demo.