Solved

How extract a substring?

Posted on 2001-07-03
18
1,165 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
  • 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

705 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

18 Experts available now in Live!

Get 1:1 Help Now