HOWTO: Search for the Last Occurrence of a Substring
ID: Q168836
|
The information in this article applies to:
-
Microsoft Visual Basic Control Creation, Learning, Professional, and Enterprise Editions for Windows, version 5.0
-
Microsoft Visual Basic Standard, Professional, and Enterprise Editions, 16-bit and 32-bit, for Windows, version 4.0
-
Microsoft Visual Basic for Applications, used with:
-
Microsoft Access versions 7.0, 97
-
Microsoft Excel 97 for Windows
-
Microsoft Excel for Windows 95, version 7.0
-
Microsoft Word 97 for Windows
-
Microsoft PowerPoint 97 For Windows
SUMMARY
The InStr() function provides a method to search for the first occurrence
of one string (substring) inside another string. However, there is no
intrinsic method to search for the last occurrence of a substring. This
article provides a sample function written in Basic.
MORE INFORMATION
There are two main methods of searching for the last occurrence of a
substring:
- Searching from the right-hand end of the string and stopping when the
first match is found.
- Searching from the left-hand end of the string until no more matches are
found, and remembering the location of the last match.
While the first method would seem the logical approach (and would be in a
language such a C that allowed direct pointer manipulation), the second
method is nearly always faster in Visual Basic using the InStr() function
to search forward through the string.
This is because InStr() is highly optimized and many orders of magnitude
faster than emulating the functionality via looping through the string
using the Mid$() function to extract each character.
The code example below demonstrates the second method.
WARNING: Microsoft provides code/macro examples for illustration only,
without warranty either expressed or implied, including but not limited to
the implied warranties of merchantability and/or fitness for a particular
purpose. This code is provided 'as is' and Microsoft does not guarantee
that the following code can be used in all situations. Microsoft does not
support modifications of the code to suit customer requirements for a
particular purpose.
NOTE: In versions of Basic that don't support the "_" line continuation
character, the split lines must be entered on a single line when typing the
code into the module.
Step-by-Step Example
- Create a new project and add a module:
Function InStrR(ByVal sTarget As String, _
ByVal sFind As String, _
ByVal iCompare As Long) As Long
Dim P As Long, LastP As Long, Start As Long
P = InStr(1, sTarget, sFind, iCompare)
Do While P
LastP = P
P = InStr(LastP + 1, sTarget, sFind, iCompare)
Loop
InStrR = LastP
End Function
- In Visual Basic only, run the project and when the default Form1 is
displayed, pause it.
- To test this function, type the following line in the Debug/Immediate
window, and then press the ENTER key:
?InStrR("The quick brown fox jumped over the lazy dog", "the", 0)
You should see 33 as the result.
Values for iCompare can be:
0 Binary comparison
1 Text comparison
2 Database Comparison (Microsoft Access)
REFERENCES
Microsoft Visual Basic online Help topic "InStr."
Additional query words:
Keywords : kbprg kbVBp400 kbVBp500 kbhowto VB4WIN VBKBProgramming vbwin
Version : WINDOWS:4.0,5.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto
|