How to remove first numbers from column which start with numbers

I have a table with a column like this

1 prova
2 prova 34 bla bla
3 prova 
bla bla
bla 44 bla 
232 bla bla 89 bla
5987 bla bla

I woukd like to delete the first numbers from fields. This is what I'm trying to achieve:

prova
prova 34 bla bla
prova 
bla bla
bla 44 bla 
bla bla 89 bla
bla bla

Thank you

CodePudding user response：

On MySQL 8 , we can do a regex replacement:

SELECT col, REGEXP_REPLACE(col, '^\\d \\s*', '') AS col_out
FROM yourTable;

Here is a demo.

On earlier versions, we have to do more work. One approach:

SELECT col,
       CASE WHEN col REGEXP '^[0-9]'
            THEN SUBSTRING(col, INSTR(col, ' ')   1)
            ELSE col END AS col_out
FROM yourTable;

And here is a demo for this second query.