Hiya,

i have a string that's pulled into my db from a Sharepoint doc lib, and it can look like the following: (simplified, of course):

word/word/word/@1234@_service_name/year/frequency/other
OR
word/word/word/service_name_@1234@/year/frequency/other
OR
word/word/word/service_name_@1234_5678_9876@/year/frequency/other

What i need to do is extract whatever is between the "@" marks. This is the most important part of the string. i could do it independently of help if the length of the text between the marks was always 4, but since it's not, so i need to ask. Would welcome your thoughts on the best syntax for this, or a push in the right direction.


Thanks from Tokyo

Hi -

Try saving this function to a standard module:

Public Function Extracto(x As String) As String
Dim y As Integer
Dim z As Integer
y = InStr(x, "@")
z = InStr(Mid(x, y + 1), "@")
Extracto = Mid(x, y + 1, z - 1)
End Function

Here are a couple of examples of how this works:

? Extracto("word/word/word/service_name_@1234@/year/frequency/other")
1234
? Extracto("word/word/word/service_name_@1234_5678_9876@/year/frequency/other")
1234_5678_9876

HTH - Bob

You can actually do this with one simple expression ....

? Split("word/word/word/service_name_@1234_5678_9876@/year/frequency/other","@")(1)
1234_5678_9876

But Split() does not work in a Query object, so if that is where you need this parsed out, then you will need to wrap above expression with a User Defined Function.

Definitely a thing of beauty!! Worked perfectly. Thank you very much!

Yep, i needed it in a query, so i've set what Bob setup for me as a function! A great piece of code, saves doing things outside of Access.

Glad it worked for you. Good luck with your project!

Best wishes - Bob

Hi,

It's possible to do this with an expression directly in the query and it will be faster :


Philippe

Thanks for that too. When it rains, it pours!

Hi noppojp,

If you're concerned with speed then I think Brent's method would be faster than any of the other methods presented.

Just my 2 cents...

Hi,



You're right, the following expression is also slightly slower...


regards,

Philippe

At this point, with only about 5000 lines i'm not too concerned with speed. If it becomes an issue, the other options will come in handy. But, first of all, i'll have to run this at work. So far, it's only been on my home PC with dummy data emulating what's at work. The big test will be this week. Thanks from Tokyo.

Hi,




We are all happy to help. Good luck with your project.

Thanks guys! Update for you all.
Facts: 5000 lines take down to 1200 via query criteria, and then run the query to parse and split out the imported text.

* Bob's suggestion: exactly 60 seconds
* Philippe's suggestion: exactly 45 seconds

The math shows that it's a 25% gain in time by using SQL rather than VB. However, i'm good either way. i tend to let queries run while i do something else anyway, so 15 seconds won't change much in my way of doing things. If we were talking 100k+ lines, i'd likely ensure the query language was used to speed it up. Both work perfectly well.

Hi,

Thanks for the update!

Cheers

It does not surprize me that a call to a User Defined Function is slower than using function calls that are inherent to the Jet/ACE database engine through the Expression Service. But so often I need more flexibility and re-usability that I a fine taking the hit in performance in this type of circumstance --- in general at least! ...

As an FYI for flexibility with extracting an element from a string, I have this user defined function...



? GetStringElement(1, "@", "word/word/word/service_name_@1234_5678_9876@/year/frequency/other")
1234_5678_9876