How extract a substring?

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.
LVL 7
EYoungAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
rspahitzConnect With a Mentor Commented:
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
 
Z_BeeblebroxCommented:
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
 
Dave_GreeneCommented:
Hey E,

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

Dave
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
EYoungAuthor Commented:
Thank you for the suggestions.  Dave, I don't know anything about XML and it's DOM.
0
 
Dave_GreeneCommented:
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
 
EYoungAuthor Commented:
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
 
PaulHewsCommented:
>>
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
 
PaulHewsCommented:
You can use Instr in a loop to find the n'th value mark.

0
 
PaulHewsCommented:
... Just like rspahitz gave you complete code for!  Nice one rspahitz.
0
 
rspahitzCommented:
Yea...I didn't have a chance to test it, but it should be close.
0
 
Dave_GreeneCommented:
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
 
EYoungAuthor Commented:
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
 
EYoungAuthor Commented:
My thanks to all for the help.

Dave, my direct email address on Mondays and Tuesdays is: Eric.Y@MackenzieWarehouse.com
0
 
rspahitzCommented:
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
 
rspahitzCommented:
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
 
rspahitzCommented:
(oops...sorry...wrong question)
0
 
EYoungAuthor Commented:
I have asked this Access question separately just now.  Please see:  http://www.experts-exchange.com/jsp/qManageQuestion.jsp?qid=20145389

Thanks
0
 
Dave_GreeneCommented:
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
All Courses

From novice to tech pro — start learning today.