asked on

ADO and Access: Obtaining substring from end of string based on delimiter

Using an ADO SQL query against MS Access, how do you obtain a varchar substring from the end of a varchar field based on a delimiter?

I have searched for an answer in a number of ways, trying to find alternates for the InStrRev, Reverse and StrReverse methods (all of which are unsupported by Access via ADO), but I have not been able to find a previously documented direct answer to this question.

Please note again, for an acceptable answer, this must be performed in a SELECT statement via ADO against Microsoft Access. I can not use VBA or VB code for this solution (if that was all I needed, I would not be posting this).

More Info:
- Visual Basic 6.0 SP6
- MS ADO 2.7
- MS Access 2000 (and newer)

Thanks for any experienced insight on this delima.

JavaRoss

ASKER

Ah, I forgot an example, so here it is:

Sample Data [varchar(250) SampleColumn]:
root.application.section.subsection.item
root.application.section.subsection2.dataitem
root.application.anothersection.myleaf
root.application.lastsection.subby.lala

Sample desired value of SELECT
item
dataitem
myleaf
lala

GRayL

You say they are unsupported by Access ADO but what about using the functions in a UDF from ADO?

Leigh Purvis

Have you tried having a saved query in Access - using a pretty standard method like

SELECT Mid([YourField],InStrRev([YourField],'.')+1) FROM tblTable WHERE InStr([YourField],'.')>0

And using your ADO connection to just open that query?
See if Access can help you as much as possible. :-)

(And if need be - then use your own UDFs in the query - Ray's advice).

JavaRoss

ASKER

Thank you GRayL and LPurvis,

I have considered the possible option of user defined functions, however it is an undesirable solution because the particular SELECT statement that I am attempting to create is part of an upgrade process that will pull information from an existing previously installed/deployed customer database for use in the creation of a new upgraded table/database. I am attempting to form the SELECT statement in such a way that the resulting recordset may be passed to the same Load method in my VB code that is used to load the data from databases using the new database schema, rather than creating seperate special-cased load methods for each upgrade scenario, polluting my object model and making a maintenance headache.

I will look into that option further, however, while waiting to see if anyone has additional comments, suggestions or insights.

JavaRoss

ASKER

Since this seems to be stumping people, I'll raise the point value to 250.

In looking into the user defined function option, it does not appear to be working out to well. I have tried to create a user defined function and I tried creating a procedure, both of which failed. The procedure failed for the same reason that the SELECT fails - it stated the InStrRev function was undefined. The user defined procedure failed because it was unable to create the defined return table, so it may just be a syntax error on my part.

I have progressed along a different pass to side-step this issue, but I would still like to find a solution in case I need it now or later.

GRayL

Post your UDF and I may be able to help debug it.

ASKER CERTIFIED SOLUTION

Leigh Purvis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JavaRoss

ASKER

Thanks LPurvis,
Admittedly, I'm one of the ones that avoids DAO. My current solution is pure ADO and I am loathe to add additional dependencies, etc. In working through this, I re-evaluated a bit and ended up enhancing my primary load method to more robustly accept values and interpret them as needed, giving me more leeway in what values I pull in.