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

Premium Plugins Cut Half In Price!

Premium Plugins Cut Half In Price!

By Strictly-Software

Just to let you know the Premium Plugins for Wordpress, Strictly AutoTags and Strictly TweetBOT have been cut in price, by HALF!

These two plugins which can be used in conjunction with each other to fully automate a site are now only £20 each and can be purchased from my main sites secure payment page.

With both of these plugins together (both for PHP 5 and 7), feed content can be automatically loaded with a feed loading plugin before tags automatically being added to the articles with Strictly AutoTags.

Then you can Tweet out to one or more accounts different Tweets, with different content, shortened or not, with a time delay to stop Twitter Rushes with Strictly TweetBOT.

The premium plugins have many more features than the free versions and since Wordpress has taken them off their site, despite over half a million downloads, you can only get them from my site.

Try the free versions first if you want, however the options in the premium versions are there for performance, extra functionality and speed.

HALF PRICE PLUGINS TO AUTOMATE YOUR WORDPRESS SITE!

What more can you want if you work with Wordpress and feeds to give yourself good SEO, tagging and social media presence automatically?

Link your Twitter account up with a Facebook page and you can post from your site to Twitter to Facebook all whilst you work or relax.

Safely knowing you will grow your site with new tags, limit the number of deeplinking to prevent single new tags creating pages, and get your tags turned into Twitter #hashtags!

What more can you want?

Check out my Facebook page for more info!

Web Developing - My 10 Golden Rules For Developers

Ten Golden Rules for any Web Developer

When you have developed code for a living for the best part of 18 years you soon come up with a list of rules that help keep you sane and prepare you for the next disastrous project coming your way.

As Bismark said - "The wise man learns from the mistakes of others."

So, don't learn from your own mistakes, try and get it right first time.

Here are my own little rules to help you from learning from your own mistakes.

1. Always add comments.

The more comments the better. There is nothing worse than coming back to a bit of complicated code you or someone else wrote years ago and have no idea why they wrote it that way.

Comments can always be stripped before roll out but on a development system they are a life saver especially if you have had to do something out of the ordinary because of some uncommon situation.

2. Format your code.

Proper indentation and casing is most definitely above cleanliness when compared to Godliness.

Not only does it make the code easier to read but it looks better and prevents developers with mild OCD from stressing out and having to spend too much time re-formatting before they can even help you debug that mess you call work.

3. Don't re-develop the wheel.

Having said that don't put up with someone else's flat tyres.

People use libraries because it saves them time but every library has an author and no-one is infallible. The benefits to using your own code is that you know how every line works and can fix bugs immediately. I have not yet come across a 3rd party library that didn't have at least one major flaw and there is a reason new libraries constantly appear in the first place.

Not only will you learn more by writing your own code but you won't have to rely on someone else to fix it all when it goes wrong.

4. Cherish the anally retentive customer.

Whilst anally retentive customers may seem like a pain in the behind in reality you should cherish the fact that they actually know what they want (see point 5).

A customer that knows exactly what he wants up front and is prepared to sign off a spec and keep to it is a rare occurrence and you should be prepared to put up with their constant emails and calls during development.

You should do this because you know that once you have delivered everything they want - even if it looks like a bag of shite and acts like a bag of shite. If it was a bag of shite that they really really wanted in the first place then it will be your bag of shite that makes them happy and brings home the bacon.

A happy customer is one that doesn't ring you constantly months after the live date asking for little changes and wondering if they just have X, Y and Z all for free because they didn't think ahead earlier.

5. Most customers know Jack

The majority of customers will know next to nothing about the system they want and expect you to know how their business is run.

Getting a properly defined spec out of some customers is harder than milking a male cow.

Customers will give you definites and then complain about the lack of flexibility.

They will expect gold plating but only want to pay for metallic.

They will expect you to see into the future and know their business requirements and have them programmed before they do.

You should be prepared for this by building in ultimate flexibility at the earliest opportunity so that when they eventually change their minds you don't have to re-develop their system from the ground up.

6. Ignore No and plan for Yes

When you ask your boss if the system you are working on needs to do X and he says categorically NO. Be prepared for him to change his mind a month or two down the line when the next sale depends on that feature.

Sales people don't care how long it took you to develop something or how hard it was to develop it. All they care about is getting their next commission.

Be prepared to spend months developing features that are used purely for selling a product rather than for their usefulness. Take that opportunity to develop said feature in a new language or use the opportunity to learn a new API and incorporate it into your code. Even if the feature isn't used at least you have learnt a new skill for your CV.

7. Automate, automate, automate.

There is nothing worse as a developer spending time doing all the monotonous tasks like building Class structures, input forms and CRUD stored procs when you could be working on a cool widget, stealthy scraper or funky functionality that actually engages your mind.

For this reason you should automate the CRUD so you can spend your time on the interesting work. There are many tools available to automate code outlines based on database structures and if you always code to a template you should be able to rattle off the basics quickly so that your time is better spent on the interesting aspects of a project.

8. A Metro with a Ferrari engine still looks like a Metro. A Ferrari with a Metro engine still pulls the birds.

Most end users, customers and bosses don't care about the cleverness of your code or how many lines you have had to write to make that balloon go pop. All they care about is how the site looks.

Front end's matter because that's the part the user sees, whereas back-ends are important because they make it all work. However good the database, business logic and code is under the covers if the front end looks like a four year old's first drawing then no-one will care.

In the same way as a sales person takes all the money from your hard work. Your high performing, code wise back end is just the engine that props up the front end that everyone looks at and goes "ooh isn't that nice". OR more importantly makes them decide that your site is rubbish purely because it looks bad.

Remember you could have the best code in the world but if it has a crap design people won't appreciate it.

9. Be prepared to be unappreciated and under paid.

Sales people and bosses will milk your talent to enrich themselves whilst giving you platitudes and praise but keeping the hard cash for themselves.

Making good money from web development is very hard unless you come up with the newest idea that Google might purchase down the road or work for big well paying company.

It's very easy to steal code from the web and with open source it's increasingly hard to make good money when people are throwing their code around for free in the hope that someone likes it enough to pay them for an upgrade or support.

10. Learn to code for fun.

If you don't enjoy coding then you won't be prepared to put the spare time in to make your own projects on the side. No-one ever got rich working for someone else but being able to give up your job to work on your code is impossible unless you have money to back your work. Being able to code for pleasure makes working on your own projects in your spare time a whole lot easier.

Those are my top 10 tips for helping you get through life as a web developer. If you have your own tips please respond using the comment section.