Pattern Matching and Data wrangling in SQL-CodePudding

I'm a beginner in SQL. I'm trying to write a query who shows goals by player from a dataset I found on Kaggle. The tricky part is that goals are represented as strings in the column "Event". If a specific player scored two goals in the same match, the "Event" column will show for him "G43' G87' when 43 and 87 are the goals minutes. My goal is to count goals. Hence, actually I can count the amount of time the term G'is shown in the Event Column for any player.

My query will look something like that:

SET @player = 'Lionel Messi';
Select Player, Count(goals) as Goals_Count
From WorldCupPlayers
Where Player=@player
Group By Player
ORDER BY Goals_Count DESC;

The challenge is to solve the goals counting logic in SQL. I will be thankful for any assistance. Thanks

CodePudding user response：

Here's one way to count the number of G's in "event"...

    select player, 
    sum(length(event) - length(replace(event, 'G',''))) as nbr_of_goals
    from table_name
    where ....
    group by player