mysql - 如何仅从字符串字段mysql获取数字

Question

我有一个值类型为 store878 的字段。我想从 select 语句中得到 878 。如何从 select 语句中获取该数字

 select 
     store,
     address 
 from 
     detail, 
     store_number 
 where 
     store (here i would like to have number) =  store_mumber.id 

Answer 1

我还没有对此进行测试以确保其有效:

SELECT store, address 
FROM detail d
INNER JOIN store_number s
   ON CAST(SUBSTRING(d.store, LOCATE('%[0-9]%', d.store)) AS int) =  s.id 

但是您确实应该考虑更改数据库的结构。