Extracting useful data from the betting string

This post comes as a result of thinking about ways to calculate the price movements of horses, from the opening show to the SP. The opening show and other price movements aren’t currently available in Smartform as separate fields, however the opening show and other price movements are contained within the betting_text column in the historic_runners and historic_runners_beta tables, stored as a text string.

The question is how to split out the data. After a number of experiments, I came up with the following SQL to derive a usable price to go to work with. In my next post I will describe how to convert this to a decimal price and calculate the price movement from the opening show to starting price.

IFNULL(
     REPLACE(
         REGEXP_SUBSTR(
             betting_text,
             '(?<=op )\d{1,3}/\d{1,3}|(?<=tchd )\d{1,3}/\d{1,3}|(?<=op )Evens|(?<=tchd )Evens'
         ), 
         'Evens', 
         '1/1'
     ), 
     '0/0'
 ) AS derived_previous_odds

The above code looks into the string field called betting_text and tries to find the previous odds of a horse from the point at which the betting shows open on course before any given race

Here’s what each part does:

1. REGEXP_SUBSTR(...):
   This searches the text for the first match of one of the following patterns:
   • A fraction like 5/2, 11/8, etc. that appears after the word “op “ (short for “opened at”)
   • A fraction that appears after the word “tchd “ (short for “touched”)
   • The word "Evens" (which means 1/1 odds) after either “op ” or “tchd “
2. REPLACE(..., 'Evens', '1/1'):
   If the match found was "Evens", this replaces it with "1/1" to standardize it into fractional format.
3. IFNULL(..., '0/0'):
   If nothing matched (i.e., there were no odds found in the text), it returns '0/0' instead of null.

Examples

betting_text = "op 5/1 tchd 6/1"

• Match found: "5/1" (after "op ")
• Replacement: None
• Final result: "5/1"

betting_text: "op Evens tchd 11/10"

• Match found: "Evens" (after "op ")
• Replacement: "Evens" → "1/1"
• Final result: "1/1"

betting_text = "op 85/40 tchd 2/1"

• Match found: "85/40" (after "op ")
• Replacement: None
• Final result: "85/40"

betting_text = "tchd 30/100 op 4/5"

• Match found: "30/100" (after "tchd ")
• Replacement: None
• Final result: "30/100"

betting_text = "non-runner, withdrawn at start"

• Match found: None (no “op” or “tchd” with odds or “Evens”)
• Replacement: Not applicable
• Final result: "0/0" (default value when nothing matches)

This entry was posted on Sunday, May 11th, 2025 at 7:22 am. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.