The single quotes aren’t the problem. The problem is that when the template is enclosed in double quote characters, the escape characters (i.e., the backslashes) are being consumed by the YAML parser, so they’re gone by the time the template is rendered. (Or, in this case, the YAML string parser doesn’t like \d in the middle of a string.) So you need to escape the escape characters.
Also, in this case, as @tom_l suggested, apparently the * “zero or more” character isn’t known, so the + “one or more” character needs to be used instead.
So, putting it all together:
- platform: template
sensors:
miles_to_service:
value_template: "{{ state_attr('binary_sensor.330i_condition_based_services', 'oil distance') | regex_findall_index('\\d+') }}"
friendly_name: Miles to Service
unit_of_measurement: mi
icon_template: mdi:oil
Alternatively, you can just swap the single and double quote characters:
- platform: template
sensors:
miles_to_service:
value_template: '{{ state_attr("binary_sensor.330i_condition_based_services", "oil distance") | regex_findall_index("\d+") }}'
friendly_name: Miles to Service
unit_of_measurement: mi
icon_template: mdi:oil
which works because (apparently) backslash characters inside single quotes are not treated as special escape characters (and so in this case you don’t need to escape them! 
)
EDIT: I had completely looked past the other issues with the config. Thanks to @petro for pointing those out! I’ve updated this post accordingly, especially since it was marked as the solution.
Funny how you look past obvious things when focused on the original question.