Since we no longer update our LM repository we have switched to using it as a read-only pool of files. The moodle module holds an external URL which would redirect to the stored file repository/file.php?f=4000. I needed to change this to e.g. /repository/a4000/4000.xlsx

The file extension is thankfully held in the repository module table so I thought it would be quite easy to manipulate the URL to point straight to the file.

The only difficulty I encountered was extracting the number (e.g. 4000) to use in constructing the final URL.

I was pleased to discover that someone on the ever useful stackoverflow had a function for MySQL called ‘digits‘ to do exactly that.

Once the function was entered into MySQL with a tweak to allow longer strings, I repeated this task for each affected file extension:

UPDATE mdl_dumgalmaterials 
SET 
    externalurl = REPLACE(externalurl,
        'repository/file.php?f=',
        'repository/a')
WHERE icon='xlsx' ;

UPDATE mdl_dumgalmaterials SET externalurl = Concat(externalurl,'/') WHERE icon='xlsx';

UPDATE mdl_dumgalmaterials SET externalurl = Concat(externalurl, digits(externalurl)) WHERE icon='xlsx';

UPDATE mdl_dumgalmaterials SET externalurl = Concat(externalurl,'.xlsx') WHERE icon='xlsx';

Note the use of the digits function to append the file ID. You would need to be cautious and maybe find another solution if your URL contained other digits before the ID.