Data Cleansing View in MySQL

I discussed before how I picked up parkrun data from my e-mails, they don't have an API as their system was never designed to cope with the millions of people that now take part. I only want my own data so this works just fine for me. I use a Zap to pick up the e-mail and plonk it in a Google Sheet and the Keboola to process the data into MySQL and maybe soon Snowflake. Actually given the setup I have it would only take 5 minutes in Keboola to add a step to the Flow to pass the output from the view below and put it into Snowflake as a table. I am leaning more towards using Snowflake as long as Retool stays free enough for me to use as the free MySQL database has a very limited session pool and therefore limits the visualisations I can do. 

Anyway the raw data from the e-mail is useless for visuals so I processed the data in MySQL. There might be more elegant solutions but for me it was some experience in how to code this in MySQL and what functions it has. Being primarily used to Oracle it has been interesting doing this in MySQL and Snowflake. The view uses that fact that the e-mail arrives in a standard format to pull out the details I am interested in, such as location, time, parkrun number etc. and allows me to then report on these. 

The code for my view can be seen here: 

	create or replace view v_parkrun_result as
	select
	/*for Mysql
	29-04-2022 - Gary Manley - Initial Version
	*/
	substr(mailtext,
	locate('Your time was',mailtext)+14,
	8) event_time,
	cast(substr(mailtext,
	locate('Congratulations on completing your ',mailtext)+35,
	3) as DECIMAL) parkrun_number,
	-- get place
	trim(substr(mailtext,
	locate('Hello Gary',mailtext)+18,
	locate('results for event ',mailtext)
	- (locate('Hello Gary',mailtext)+18)
	)) parkrun_place,
	-- get position
	cast(substr(mailtext,
	locate('today. You finished in',mailtext)+22,
	3) as DECIMAL) parkrun_position,
	-- get total participants
	cast(substr(mailtext,
	locate('out of a field of ',mailtext)+18,
	4) as DECIMAL) total_field,
	-- age category
	substr(mailtext,
	locate('category VM',mailtext)+9,
	7) age_category,
	-- age grading (as percentage)
	cast(substr(mailtext,
	locate('You achieved an age-graded score of ',mailtext)+36,
	5) as DECIMAL(4,2)) age_rating,
	date(cast(date as datetime)) event_date
	from ext_tab_parkrun_email

view raw parkrun_results_v.sql hosted with ❤ by GitHub