Posted on 2005-05-02
Last Modified: 2008-01-09
Im trying to get a result to return only the paths out of my hard drive, through a database, I have imported all files on my hard drive into the database.

When I query through Access I use:-
     SELECT Left([path],InStrRev([path],"\")) AS Expr1
     FROM scanned
     GROUP BY Left([path],InStrRev([path],"\"));
and get 40 rows back, whatever I try in MySql I only get half the rows back. I know my probelm is cause MySql doesnt support 'InStrRev', but cant see any similar functions.

I have tried to make my own instrrev like this "length(`path`) - INSTR(reverse(`path`),"\\"))) "

InstrRev is the same as instr, but returns the last value in the string, instead of where it found it first.
Question by:tonelm54
    LVL 3

    Expert Comment

    Try this:
         SELECT REVERSE( SUBSTRING( REVERSE([path]), LOCATE("\\")+1) AS Expr1
         FROM scanned
         GROUP BY Expr1;
    LVL 3

    Accepted Solution

    oops, slight revision:

         SELECT REVERSE( SUBSTRING( REVERSE([path]), INSTR(REVERSE([path]), "\\")+1) AS Expr1
         FROM scanned
         GROUP BY Expr1;

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Need help with a query 7 53
    Relationships in MySQL Workbench 4 27
    MySQL 11 47
    Does PHPMyAdmin pose a security risk? 2 30
    Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (…
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now