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
Solved

How extract a substring?

Posted on 2001-07-03
18
1,180 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

840 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