[SOLVED] How to parse text and number using substring in MySQL?

Issue

I have thousands of records now and I am web scrapping from web site and names comes only first name or first and second name currently as: Souleymane Doukara 85 Souza 25 Souza 59 Stefano Okaka 16 Tayfur Bingol 53 Tayfur Bingol 67 Y. Sari 90 Younes Belhanda 46

I have table records in mysql as:

Table:

I wrote below query and I got results also below:

select
SUBSTRING(TRIM(SUBSTRING_INDEX(`scorera`, '  ', 1)), 1) AS 'Player',
SUBSTRING(TRIM(SUBSTRING_INDEX(`scorera`, ' ', -1)), 1) AS 'Goal Minute'
from goals;

If I use one more blank first substring line as shown *

select
SUBSTRING(TRIM(SUBSTRING_INDEX(`scorera`, '  * ', 1)), 1) AS 'Player',
SUBSTRING(TRIM(SUBSTRING_INDEX(`scorera`, ' ', -1)), 1) AS 'Goal Minute'
from goals;

I got below result as player has first name only:

How can I get correct names when player has first or first and second names as below?

Thanks for your help.

Names comes only first name or first and second name currently as:

Souleymane Doukara 85 
Souza 25 
Souza 59 
Stefano Okaka 16
Tayfur Bingol 53
Tayfur Bingol 67 
Y. Sari 90 
Younes Belhanda 46

Solution

If you want the part before the first digit as player name then this should work:

select RTRIM(LEFT(scorera, (REGEXP_INSTR(scorera, '[0-9]')-1))) AS `Player`,
SUBSTRING(TRIM(SUBSTRING_INDEX(`scorera`, ' ', -1)), 1) AS `Goal Minute`
from goals;

Fiddle

Note that this work with strings should be done on your data before inserting it in your table, otherwise using your data from your table will be painful and inefficient.

Answered By – Zakaria

Answer Checked By – Pedro (BugsFixing Volunteer)