Search string for first alpha character within a query

Except for the issue we already had with VAL, something like this:


? Instr(1,"123Xnb", mid("123Xnb",Len(cstr(val("123Xnb")))+1,1))

returns 4

but I have not fully tested this ...

mx

I'm not following you on this one.

MX,

A valiant effort, but I think it's going to break down once you hit a string that has a leading zero.

:(

Regards,

Patrick

4 is the position of the first alpha character in "123Xnb"

So, generic:

Instr(1,YourString, mid(YourString,Len(cstr(val(YourString)))+1,1))

BUT ... this will probably have the same issue with "12D01" etc that Val did in your other Q's

MP... hey ... that just a MINOR detail, lol ...



See this Q:

https://www.experts-exchange.com/questions/23492606/Val-is-returning-unexpected-results-in-a-query.html?cid=238&anchorAnswerId=21807758#a21807758


Sadly ...

Instr(1,"12D102", mid("12D102",Len(cstr(val("12D102")))+1,1))

returns 1    :-(
mx

I'm trying to experiment another way with this question...
If I reverse the string, then locate the first alpha, I then can use the Right() function to get the character from the right side of the string.  This should solve the "101D10" problem.

But what about that missing 0 issue  ?

When I reverse the string..."0101D10" I get "01D1010".  Then if I can get the location of the first D then I can use the Right() function to get 10.  So something like this....
Right(PartNumber, Instr(StrReverse(PartNumber),[A-Z]))

I just don't know if I can search for any letter A thru Z in the Instr() function.

I don't think [A-Z] will fly ....

Right, which leads us back to regular expressions...

:)

Yes I know...that is the reason for this new question.  Is there a bracket or something ath I can use in the Instr() function that will look for the first alpha character?

No

SQL Server would support this, though...

In the query, I could create 26 columns (one for every character) with the InStr() function and then sum up the 26 columns in a 27 column to get my location.   LOL :)

Instr(1,YourString, mid(YourString,Len(cstr(val(YourString)))+1,1))

This may deal with the leading zero(s) issue:

Instr(1,YourString, mid(YourString,Len(cstr(val("1" & YourString))),1))

rheitzman,
The propose of this exercise is to extract the last numeric portion of the numeric-alpha-numeric string for sorting.  Each numeric and alpha portion have various character lengths.  For example we need to take "0101D10" and get "10" as a result.
See previous posts
https://www.experts-exchange.com/questions/23492830/How-can-I-apply-a-Val-from-the-right-side-of-a-string.html
and
https://www.experts-exchange.com/questions/23491436/Part-Number-Alpha-Numeric-Sorting-Access-2003-Query.html

Hi All,

Slightly off the wall idea but would a private function be the answer?

I am still learning with VB and functions but I have managed to get to this point:

-----------------------------------------------------------------------------
Public Function findTxt(data As String) As Variant
On Error GoTo Err_findTxt

'Crated By Mark 18/06/2008
'Porpose to find the position of first text entry in a string of characters


Dim txtPos As Variant 'Return Value for Function
Dim chkChar As Variant 'Character to Check


'Initialise to Zero
txtPos = 0

'Check string passed is not null

If IsNull(data) Then
    GoTo notFound
End If

Do While Len(data) <> 0
    txtPos = txtPos + 1
    chkChar = Left(data, 1)

    If Not IsNumeric(chkChar) Then
        findTxt = txtPos
        GoTo Exit_findTxt
    End If
    data = Right(data, (Len(data) - 1))
Loop

'if this point is reached there are no charcters in the text so set return to zero

notFound:
'Assign Return Value
findTxt = 0

Exit_findTxt:
    Exit Function
   
Err_findTxt:
    MsgBox Err.Description, vbExclamation, "Delivery Data Error " & Err.Number
    Resume Exit_findTxt
End Function

----------------------------------------------------------------------

Then in your query use  findTxt([your_string])

This should return the position first (IMPORTANT POINT HERE) "Non Numeric" character in the string or zero if the string is all numeric.  However I am having difficulty if the string passed is null and keep getting errors.

So,

1. I hope this helps in some way.

2. If one of the real experts would like to either fix my version of the function or just write one that works from scratch, then I would be very interested to see how this should be done.


Cheers,

Mark.

OnALearningCurve
We were trying to get a solution wihtout calling VB mod due to the size of the list for speed.  When we called a VB for this list in post https://www.experts-exchange.com/questions/23491436/Part-Number-Alpha-Numeric-Sorting-Access-2003-Query.html it was painful to watch.

Ahhhh,

I see,

Sorry to waste your time.

Mark.

No waste...

OnALearningCurve
I like the effort...however we are trying to either get the location of the LAST Alpha character.  Note that the alpha character length is not consistent.  Ultimately we are trying to get the last numeric sequence filtered out to sort by.  I even wrote the easiest VB to get that location number in the string , and it still took 3 minutes to run the query when that field is sorted.  I'm about ready to move on and live with the generic sort.

OK Last attempt,

SET: Replace((Replace((Replace((Replace((Replace((Replace((Replace((Replace((Replace((Replace([F],0," ")),1," ")),2," ")),3," ")),4," ")),5," ")),6," ")),7," ")),8," ")),9," ")

Gives you the string with digits replaced by spaces

CHSP: IIf((Len(LTrim([SET])))=0,0,((Len([SET]))-(Len(LTrim([SET])))+1))

Gives you the location of the first non numeric character

CHFP: Len(RTrim([SET]))

Gives you the location of the last numeric character

SRT1: Int(Left([F],[CHSP]-1))

Gives you the integer value for the first set of numeric characters

SRT2: Mid([F],[CHSP],([CHFP]-[CHSP]+1))

Gives you the non numeric characters

SRT3: Int(Right([F],(Len([F])-[CHFP])))

Gives you the integer value of the last set of numeric characters

All together in a query they look like:

=================================================
SELECT STRTBL.F, Replace((Replace((Replace((Replace((Replace((Replace((Replace((Replace((Replace((Replace([F],0," ")),1," ")),2," ")),3," ")),4," ")),5," ")),6," ")),7," ")),8," ")),9," ") AS [SET], IIf((Len(LTrim([SET])))=0,0,((Len([SET]))-(Len(LTrim([SET])))+1)) AS CHSP, Len(RTrim([SET])) AS CHFP, Int(Left([F],[CHSP]-1)) AS SRT1, Mid([F],[CHSP],([CHFP]-[CHSP]+1)) AS SRT2, Int(Right([F],(Len([F])-[CHFP]))) AS SRT3
FROM STRTBL;
==================================================

Then query this result set sorting by SRT1, then SRT2 , then SRT3  and I think you get the result set you were after.

As with your VB script, I'm not sure if this will still take an age to run or not but it may be owrth a try.

See the images of the two result sets below.

Hope this helps,

Mark.
resultsimg.doc

I would suggest you add three columns (numeric,text,numeric) to your table and parse the p/n into the fields as a first pass, then do the sort.

If you are working on a master table you can filter the records get rid of the records that have already been parsed.

The code doesn't guard against non-compliant p/n strings.

PS - it is helpful if you post an MDB with sample data for problems like this.

Sub ParsePN()
    Dim t As Integer
    Dim s As String
    
    Dim RS As New ADODB.Recordset
    
    RS.Open "SELECT PN, S1, S2, S3 FROM Parts WHERE S2 IS NULL", CurrentProject.Connection, -1, adLockOptimistic
    Do Until RS.EOF
        s = RS!PN
        t = 2
        Do While IsNumeric(Mid(s, t, 1))
            t = t + 1
        Loop
        RS!S1 = Left(s, t - 1)
        s = Mid(s, t)
        t = 2
        Do While Not IsNumeric(Mid(s, t, 1))
            t = t + 1
        Loop
        RS!S2 = Left(s, t - 1)
        RS!S3 = Mid(s, t)
        RS.Update
        RS.MoveNext
    Loop
End Sub

Select allOpen in new window

db6.mdb

shrimpfork,

To get this sorting quickly (or, if not quickly, at a pace faster than "glacial"), then I think you are going to have
to add three columns to the table, Part1 (type: Long), Part2 (text), and Part3 (Long).  Then, using the RegExpFind
function from https://www.experts-exchange.com/questions/23491436/Part-Number-Alpha-Numeric-Sorting-Access-2003-Query.html#21804549, run a query like this:

UPDATE YourTable
SET Part1 = Val(RegExpFind(PartNum, "^\d+", 1)), Part2 = RegExpFind(PartNum, "[A-Z]+", 1, False),
    Part3 = Val(RegExpFind(PartNum, "\d+$", 1))

Then, do your sorting on those three columns.  Make sure to index them.

I will say, though, that I do not like this idea--it creates data integrity hassles on inserts and updates.
That can be handled if all of your inserts and updates are mediated by forms, but if not then...

Regards,

Patrick

OnALearningCurve
This solution worked great.  Using this I am able to get the location of the 1st Alpha Character quickly in the query.  (I'm actually trying to get the last Alpha Character location, but by a string reversal I got my data that I was looking for.  Thanks.

Mark,

I am not worthy :)

Regards,

Patrick

Patrick,

I am honoured.

Mark.

shrimpfork,

Glad I could help.

:)

Mark.