Solved

How extract a substring?

Posted on 2001-07-03
18
1,195 Views
Last Modified: 2012-05-04
Basics:  VB6 sp5 on NT4.0

Question:  How can I extract a sub string from between two particular ASCII characters (value marks)?

The input is a long string that has many sub strings in it.  Each sub string is separated by a 'value mark'.  For example, I might want to extract the 2nd sub string (that sub string between the 1st value mark and the 2nd value mark.  There is no leading value mark.)  Or I might want to pull the 23rd sub string (that sub string between the 22nd value mark and the 23rd value mark.)  

As there will be over 100,000 long strings (records) that I will have to process, I want something that is quick as opposed to counting where each value mark is in the long string and then using the MID function based on the count.  I will need to extract about 24 sub strings from each long string.  Also, each sub string can vary in length.

Thanks for the help.
0
Comment
Question by:EYoung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 4
  • +2
18 Comments
 
LVL 7

Expert Comment

by:Z_Beeblebrox
ID: 6250049
Hi,

The easiest is to use the split command

arr = split(LongString, ",") where "," is your separator.

This is however the slowest method. Iterating through each character checking is much faster, particularly if you only want certain items. You may find that using instr is a bit faster but it will probably make your code much messier.

Zaphod.
0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6250061
Hey E,

I would recommend XML and it's DOM, for such a process.  Let me know if you need more info.

Dave
0
 
LVL 7

Author Comment

by:EYoung
ID: 6250066
Thank you for the suggestions.  Dave, I don't know anything about XML and it's DOM.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6250102
Ok, can you tell me what exactly you are trying to accomplish, then I'll tell you whether an XML solution is applicable.

Thanks
0
 
LVL 7

Author Comment

by:EYoung
ID: 6250146
I am using some jBase object code to read records from a Unix system and port them into an MS Access table.  This reading/porting is coming along just fine.

But writing the records into the Access table is proving to be time consuming.  Apparently, Access slows down significantly as the number of fields in the Access table increases.  I have run some time tests on the Access table and when the number of fields is small, say under 10, the speed is quick even with 100,000 plus records.  But when the number of fields is bumped to 50 or 75, the speed significantly degrades.  I am not sure what I can do about that but I can keep the extracting of the sub strings from the long strings down to as low a time as possible.
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 6250148
>>
As there will be over 100,000 long strings (records) that I will have to process, I want something that
is quick as opposed to counting where each value mark is in the long string and then using the MID function
<<

Instr is *much* faster than counting characters use instr to find the locators and then use Mid

Dim lngStart as long, lngEnd as long

lngStart = Instr(1, strBigStr, strStart)
lngEnd = instr(lngStart, strBigStr, strEnd)
strMid = Mid$(strBigStr, lngStart + 1, lngend - lngstart -1)
0
 
LVL 22

Accepted Solution

by:
rspahitz earned 50 total points
ID: 6250157
Try this:


Private Function GetSubstring(ByRef SearchString as string, ByVal Delim as string, ByVal ItemCount as integer) as String
 ' Pass a reference to the SearchString rather than by value.  This speeds things up but has the potential to change the string if you're not careful
 ' Delim is the starting delimiter; if you need two, pass separately
 ' ItemCount is the element number you want (1st, 23rd, etc)

  dim lRefPosit as long
  dim iCurrentItem as integer
  dim lEndPosit as long

  lRefPosit = 0
  iCurrentItem = 1

  do while iCurrentItem < ItemCount
    lRefPosit = instr(lRefPosit + 1, SearchString, Delim)
    if lRefPosit = 0 then
      exit do
    end if
    iCurrentItem = iCurrentItem + 1
  loop

  if lRefPosit = 0 then
    GetSubstring = ""
  else
    lEndPosit = instr(lRefPosit + 1, SearchString, Delim)
    if lEndPosit = 0 then
      lEndPosit = len(SearchString) + 1
    endif
    GetSubstring = mid$(SearchString, lRefPosit + len(Delim), lEndPosit - lRefPosit - 1)
  end if
end function

To use:

MyDelim = "|"
MyItem = 23
Text1.Text = GetSubstring(BigString, MyDelim, MyItem)
0
 
LVL 38

Expert Comment

by:PaulHews
ID: 6250159
You can use Instr in a loop to find the n'th value mark.

0
 
LVL 38

Expert Comment

by:PaulHews
ID: 6250162
... Just like rspahitz gave you complete code for!  Nice one rspahitz.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6250185
Yea...I didn't have a chance to test it, but it should be close.
0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6250189
XML is not going to help you with this problem E, I would suggest moving away from an Access database if at all possible.  Access is the point of failure for many projects with a lot of data.

Good luck!

-D
0
 
LVL 7

Author Comment

by:EYoung
ID: 6250238
Dave - I am not sure what other storage program to save the data in if I don't use Access.  Any ideas?  The app will be loaded onto laptops for the salesguys to use so SQL Server is out.  Need something simple and easy to work with.

Thanks
0
 
LVL 7

Author Comment

by:EYoung
ID: 6250246
My thanks to all for the help.

Dave, my direct email address on Mondays and Tuesdays is: Eric.Y@MackenzieWarehouse.com
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6250257
Thanks, EYoung.

Access is not necessarily bad, but will certainly be slow when dealing with lots of records.

I hope my solution is quick enough for your needs.  And please properly test it before deploying it.  There may be some problems with things like negative item number values, etc., so you may also want to add error trapping.

0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6250284
I just tried this sendkeys on Win2000 and it seems very flaky.  It seems to be rejecting every key sent!

P.S.
The "@echo" doesn't cause problems at the DOS command line.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6250286
(oops...sorry...wrong question)
0
 
LVL 7

Author Comment

by:EYoung
ID: 6250311
I have asked this Access question separately just now.  Please see:  http://www.experts-exchange.com/jsp/qManageQuestion.jsp?qid=20145389

Thanks
0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6250321
How about uploading everything into SQL Server and have access on the salespeoples laptops.  you could then link the access DB to the SQL Server....

Just a thought...
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month6 days, 23 hours left to enroll

622 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