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;
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.