0

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.

  • Google. See if these help http://www.delphibasics.co.uk/RTL.asp?Name=LastDelimiter and http://www.delphibasics.co.uk/Method.asp?NameSpace=System&Class=String&Type=Class&Method=LastIndexOf – June7 Jun 28 '20 at 17:48
  • Hey thanks for the suggestions, I would like to do this query in SQL. Im not sure how I can make use of these functions without creating a temporary field in my table. – Oliver Hope Jun 28 '20 at 17:52
  • Perhaps have to build custom function then call function from query. If this were Access, could call a VBA custom function. – June7 Jun 28 '20 at 18:09
  • Do you have any Idea where I would build such a function for Tado components? – Oliver Hope Jun 28 '20 at 18:18
  • No idea at all. – June7 Jun 28 '20 at 18:36

0 Answers0