A little piece of SQL and a Regular Expression that saves me a hell of time

Betting, SQL and Regular Expressions

By Strictly-Software

Following on from the previous post, now that I am a professional trader on Betfair, or rather a bettor with some good luck, I managed to pick the 33/1 winner of the Derby out on Saturday somehow, I am always working on my code and my Automatic betting BOT which can TRADE for me on the Betfair Exchange.

However as I also run a Facebook page where I post up free tips from myself, and international tipster friend and other "pro" tipsters > https://www.facebook.com/ukhorseracingtipster/ I have to work on my social media quite a lot.

One of the things I had to do every night was get up at 12am to set my BOT running to import the days race cards, runners, prices and related statistics that help my systems pick out horses to bet on, and therefore to post up to tip.

However one of the most annoying jobs I found myself repeating was every night having to go to SportingLife.com and the daily race card page, copying out UK and Irish races, reformatting it and then adding it in to one of the 1st posts of the day.

Now I have tweaked the import process so I can import races from tomorrow, or 2 days away whenever I like rather than waiting until midnight just to get the current days races. I can now insert race cards for days ahead whenever I like and just concentrate on the betting and tipping. Running the BOT just to get up to date prices etc.

The post would always start like this....

Monday's racing comes from Gowran Park, Leicester, Lingfield Park, Listowel, Pontefract and Windsor.

Which means re-ordering the copied text into alphabetically listed race courses, adding in the day of the week and of course changing the last course name so it doesn't precede a comma but the word "and x" and have a full stop after it.

Not much work you might think to yourself. Well no not really, fun with refreshing pages that don't have data and are stuck in some cyclical mode, but it can take up to 20 minutes.

Therefore today I wrote a small piece of SQL that can work on any days data already imported to format this sentence correctly. I could have done it without a regular expression but then where would the fun be in that?

So here is the code:

DECLARE @Courses varchar(255),@Message varchar(255), @day varchar(15), @racedate date, @Days int
SELECT	@Days = 0 -- how many days into the future do you want data for - put 0 for today
SELECT  @Racedate = CAST(DATEADD(DAY,@Days,GETDATE()) as DATE) -- convert GETDATE() (todays datetime) into a DATE format
SELECT  @Day = DATENAME(weekday, @Racedate) -- get the current weekday for the date above
SELECT	@Courses = COALESCE(@Courses + ', ',' ') +  CourseName -- build up a CSV of courses
FROM	RACES as ra with (nolock)
JOIN	COURSES as c with (nolock)
	ON	ra.CourseFK=c.CoursePK
WHERE	Abandoned=0
	AND Racedate = @Racedate
GROUP BY CourseName
ORDER BY CourseName 
-- get the last course in the list, I could have used a UDF with comma counting but that would mean replicating the above code, easier in a regex
SELECT @Courses = [dbo].[udf_SQLRegExReplace]('(^[\s\S]+?)(?:, ([^,$]+?$))',@Courses,'$1 and $2')
SELECT @Message = @Day + '''s racing comes from ' + LTRIM(@Courses) + '.'
SELECT @Message

If you want to know what the regular expression below does:

SELECT @Courses = [dbo].[udf_SQLRegExReplace]('(^[\s\S]+?)(?:, ([^,$]+?$))',@Courses,'$1 and $2')

Then watch out for hopefully a soon coming Amazon book I am writing on getting started with regular expressions I know there are loads out there but for me personally I learn by doing things not reading things and this is what customer type the book is aimed at. 

It gets you writing regular expressions straight away and then working out what they do rather than just listing all the possible flags, identifiers and other boring stuff that a lot of reference books contain.

If you have a quicker RegEx for doing the changing of the last word etc then please post it in the comment section. I am always willing to look at other ways of doing things.

If you are into betting then always check out my facebook page for UK, Irish, French and International tips > https://www.facebook.com/ukhorseracingtipster/

By Strictly-Software

© 2021 Strictly-Software