6

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?

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • http://stackoverflow.com/questions/4537994/php-split-string-into-integer-element-and-string – R R Jan 03 '14 at 07:36
  • http://www.sitepoint.com/forums/showthread.php?939700-How-do-I-Split-an-alphanumeric-string-into-array – R R Jan 03 '14 at 07:37
  • 1
    possible duplicate of [Removing numbers from string in mysql](http://stackoverflow.com/questions/13855053/removing-numbers-from-string-in-mysql) – Saharsh Shah Jan 03 '14 at 07:38
  • 2
    @mrcoder I seek an SQL-only solution. Your links are for application code solutions – Bohemian Jan 03 '14 at 07:41
  • 1
    See the answer I wrote [*here*](http://stackoverflow.com/a/11380726/1057429) - just remove the `LENGTH` part and you'll get what you wanted. – Nir Alfasi Jan 03 '14 at 07:50

1 Answers1

5

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.

Alma Do
  • 37,009
  • 9
  • 76
  • 105
  • +1ed but just noticed extra care needs to be taken for strings ending in `0`, could append a `1` to the end of each value and then trim it after the `CAST`. – OGHaza Jan 03 '14 at 09:35
  • Yep, it's a bit 'bully' solution - also need to care about data type length since string can hold anything while integer/float are limited – Alma Do Jan 03 '14 at 09:44