regex - how can extract part of a text from a field in mysql? -

regex - how can extract part of a text from a field in mysql? -

i have fields this:

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

i want extract rows have digit in name , field contains number this

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

how can find records have digit , extract digit new field?

here way 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) ) number users having number <> '' ;

mysql regex regexp-substr