如何从mysql中的字段中提取文本的一部分？

Question

我有这样的字段：

-----------------
   id    |  name  
-----------------
   1     |  name123  
-----------------
   2     |  name  
-----------------
   3     |  name456  
-----------------
   4     |  name  

我想提取名称中包含数字和包含数字的字段的行，如下所示

------------------------------
   id    |  name     | number
-----------------------------
   1     |  name123  |  123
-----------------------------
   3     |  name456  |  456

我们如何找到包含数字的记录，并将数字提取为新字段？

Answer 1

您可以在int上使用MySQL的字符串转换来去除名称，如下所示

SELECT 
    t.id, 
    t.name, 
    REVERSE(REVERSE(t.name)+ 0) AS num,
    REPLACE(t.name,REVERSE(REVERSE(t.name)+ 0),'') AS actualname
FROM foobar t
HAVING num <> 0

诀窍是通过添加0 mysql来比较名称中的数值...但是名称必须以数字开头...所以我把它倒过来计算然后再倒过来。备注你所有的名字都必须以名字开头，以数字结尾，这样才能适用于所有人

编辑：

既然你说有些可以以数字开头，而另一些可以以数字结尾..那就试试这个

SELECT 
    t.id, 
    t.name, 
    REVERSE(REVERSE(t.name)+ 0) AS num,
    REPLACE(t.name,REVERSE(REVERSE(t.name)+ 0),'') AS actualname
FROM foobar t
HAVING num <> 0
UNION ALL
SELECT 
    t.id, 
    t.name, 
    t.name + 0 AS num,
    REPLACE(t.name,t.name + 0,'') AS actualname
FROM foobar t
HAVING num <> 0

Answer 2

下面是使用mysql的另一种方法

SELECT 
  id,
  name,
  SUBSTRING( 
    name,LEAST (
      if (Locate('0',name) >0,Locate('0',name),999),
      if (Locate('1',name) >0,Locate('1',name),999),
      if (Locate('2',name) >0,Locate('2',name),999),
      if (Locate('3',name) >0,Locate('3',name),999),
      if (Locate('4',name) >0,Locate('4',name),999),
      if (Locate('5',name) >0,Locate('5',name),999),
      if (Locate('6',name) >0,Locate('6',name),999),
      if (Locate('7',name) >0,Locate('7',name),999),
      if (Locate('8',name) >0,Locate('8',name),999),
      if (Locate('9',name) >0,Locate('9',name),999)
    ),LENGTH(name)
  ) as number
from users 
having number <> '' ;

Answer 3

另一种方式，假设你想要的数字在字符串的末尾。REVERSE()将数字部分放在前面，然后转换()使其成为数字并去掉文本，然后再次使用REVERSE()，其中name以数字结尾。尽管感觉像是一堆乱七八糟的东西：

select id, name, reverse(convert(reverse(name),signed ))
from tbl
where name REGEXP '[0-9]+$';  

SQL Fiddle Example