从列中提取字母和数字部分 [英] Extracting alpha and numeric parts from a column

问题描述

我有一个表tab1，其中的列col1具有复合的字母-然后-数字值，如下所示:

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.

如果两个值的开始和结束位置一致，则可以使用substring()完成该工作，因为您会看到数字部分的开始有所不同.

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?

推荐答案

是的，是:

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

-仅在您的列中包含字母然后是数字(如您所述)时才有效.这就是为什么需要双 REVERSE() 的原因(否则CAST()将无效).选中此演示.

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

这篇关于从列中提取字母和数字部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！