I need to find the index of the last delimiter in a string using SQL. I am using Delphi 10.3 with the TADO components to connect to MS Access. I have found several solutions that work in Access but doesn't work in my TADOquery.
Here is a query that works in MS Access
SELECT Count(*) AS NOSpots, mid([Street Address], instrRev([Street Address],",") + 1) AS Country
FROM Spots
GROUP BY mid([Street Address], instrRev([Street Address],",") + 1);
I have also tried using instr(string, substring, - 1) and StrReverse however non of these functions are compatible with TADO components. I get a 'Unknown Function' error.