Home > Software engineering >  How to parse text and number using substring in MySQL?
How to parse text and number using substring in MySQL?

Time:03-15

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:

scorera
M. Ozil 50
M. Ozil 40
M. Ozil 46
M. Ozil 31
M. Ozil 30

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;
Player Goal Minute
M. Ozil 50 50
M. Ozil 40 40
M. Ozil 46 46
M. Ozil 31 31
M. Ozil 30 30

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:

Player Goal Minute
M. 50
M. 40
M. 46
M. 31
M. 30

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

Player Goal Minute
M. Ozil 50
M. Ozil 40
M. Ozil 46
M. Ozil 31
M. Ozil 30

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

CodePudding user response:

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.

  • Related