Instr(1, Original, "PatternWithWildCards") possible??? 1

[waldemar]

Anbody has an idea how it is possible to find substrings with wildcard patterns?
For example, I am looking for a five digit Number ([0-9][0-9][0-9][0-9][0-9]), can this be done with Instr() ?

 

[Steve101]

Cant use wildcards with instr, but for what you want to do, use the LIKE operator.

For example, if

txtYourNumber = "12345" then

YourResult = txtYourNumber LIKE "[0-9][0-9][0-9][0-9][0-9]"

will return a result of true, as will

YourResult = txtYourNumber LIKE "[0-9]2345"

as will

YourResult = txtYourNumber LIKE "[0-9]???[0-9]"

Hope this helps,

Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[BlackKnight]

Hi,
The small problem with the above method is that it relies on the fact the string is only a number so, in this case you could use VAL(). If you want to get the n-digit number(s) from the string then you could try something like this:

Private Function GetNumber(ByVal Num As String, ByVal NumDigits As Long) As String
'Returns a comma-delimited string of numbers or a null if none are found.

Dim Digit As String, NumList As String, WCard As String
Dim Ch As Integer
Dim NumLen As Long
Dim NumExists As Boolean

NumList = ""
If Not IsNull(Num) Then
NumLen = Len(Num)
'Build numerical wildcard string.
For Ch = 1 To NumDigits
WCard = WCard & "[0-9]"
Next Ch

'Check if at at least one Numdigits-long number exists in the passed string.
NumExists = Num Like "*" & WCard & "*"
If NumExists Then
'Get the numbers.
For Ch = 1 To NumLen
Digit = Mid$(Num, Ch, 1)
Select Case Asc(Mid$(Num, Ch, 1))
Case 48 To 57
NewNum = NewNum & Digit
Case Else
If Len(NewNum) = NumDigits Then
'You could use an array here instead.
NumList = NumList & NewNum & ","
End If
NewNum = ""
End Select
Next Ch
'Remove trailing comma
NumList = Left$(NumList, NumLen - 1)
End If
End If
GetNumber = NumList
End Function

Have a good one!
BK

 

[Steve101]

Blacknight,
The val function will not check whether the number is 5 digits or not (without further coding). It will also allow a leading -ve sign and an embedded decimal point, both of which break the allowable patterns. With my short response, I've simply answered the posted question, using the wildcard specifications as provided, to try to remain within the bounds suggested by the question.

Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[BlackKnight]

Hi,

Yes, I agree re the val() function as is but like I stated I assumed as you did in your proposed solution that the string only contained a 5 digit number. Your solution will not work if there other characters aside from the number unless you use the *. My soltion will work regardless of what is in the string.
Yes, short and simple is always a good thing as long as it works. <g>

Have a good one!
BK

 

[Steve101]

Quote:

&quot;For example, I am looking for a five digit Number ([0-9][0-9][0-9][0-9][0-9])&quot;

My response; use:

YourResult = txtYourNumber LIKE &quot;[0-9][0-9][0-9][0-9][0-9]&quot;

YourResult returns true or false, which as I understand it is exactly what the user wants. This is a data validation exercise, not an attempt to convert something to a number.

Anyway, lets hear from waldemar as to his opinion.

Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[Steve101]

BK,
I've just re-read the post. Oops. I hav'nt really answered the question as posed. Need to work on those reading skills.
Be well,
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[waldemar]

Thanks for all those responses!

Well, Me.strDescription.Value Like &quot;*[0-9][0-9][0-9][0-9][0-9]*&quot; did part of the job, but now I'm looking for the string that was found... I guess BlackKnights routine will do this? Will try it out...

 

[Steve101]

Waldemar,

Here is an extention of the LIKE pattern approach, which I find attractive because it makes full use of the inherrent pattern matching capability built into the language, and consequently results in a short and simple but powerful solution.

[tt]
Function FindPatternInString(Strng, LenPattern, Pattern)
'-------------------------------------------------------------
'Locate a wildcard pattern of a certain length in a string.
'Returns the starting position if the pattern is found;
'zero otherwise.
'Not optimised, and assumes implicit declarations of
'variables
'
'Sample invocations:
'
'? FindPatternInString(&quot;ABCXXX1-2&quot;,3,&quot;[0-9]?[0-9]&quot returns 7
'? FindPatternInString(&quot;ABCXXX1-2&quot;,3,&quot;[A-Z][0-9]?&quot returns 6
'? FindPatternInString(&quot;ABCXXX1-2&quot;,3,&quot;B*&quot returns 2
'? FindPatternInString(&quot;ABCXXX1-2&quot;,3,&quot;BZ?&quot returns 0
'-------------------------------------------------------------
matchFound = False
For i = 1 To Len(Strng) - LenPattern + 1
StrngToMatch = Mid(Strng, i, LenPattern)
If StrngToMatch Like Pattern Then
matchFound = True
Exit For
End If
Next i
If Not matchFound Then
FindPatternInString = 0
Else
FindPatternInString = i
End If

End Function
[/tt]

Food for thought,
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[waldemar]

Cheers Steve! I modified your function a little bit so it returns the whole string that was found - this way I can compare everything and not just numbers:

Function FindPatternInString(Strng, LenPattern, Pattern) As String
'-------------------------------------------------------------
'Locate a wildcard pattern of a certain length in a string.
'Returns the starting position if the pattern is found;
'zero otherwise.
'Not optimised, and assumes implicit declarations of
'variables
'
'Sample invocations:
'
'? FindPatternInString(&quot;ABCXXX1-2&quot;,3,&quot;[0-9]?[0-9]&quot returns 7
'? FindPatternInString(&quot;ABCXXX1-2&quot;,3,&quot;[A-Z][0-9]?&quot returns 6
'? FindPatternInString(&quot;ABCXXX1-2&quot;,3,&quot;B*&quot returns 2
'? FindPatternInString(&quot;ABCXXX1-2&quot;,3,&quot;BZ?&quot returns 0
'-------------------------------------------------------------
matchFound = False
For i = 1 To Len(Strng) - LenPattern + 1
StrngToMatch = Mid(Strng, i, LenPattern)
If StrngToMatch Like Pattern Then
matchFound = True
FindPatternInString = StrngToMatch
Exit For
End If
Next i

End Function


Regards

 

[Steve101]

Yes; good idea to return the actual match string. Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au