Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2870
  • Last Modified:

Search string for first alpha character within a query

How can I use Instr() to find the location of the first alpha character in a string?

For example, can I use some sort of bracketed group, letters A-Z, in the string expression sought area of the Instr() function?  I'm trying to do this within a query.
0
shrimpfork
Asked:
shrimpfork
  • 10
  • 6
  • 6
  • +2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
0
 
shrimpforkAuthor Commented:
I'm not following you on this one.
0
 
Patrick MatthewsCommented:
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
MP... hey ... that just a MINOR detail, lol ...



See this Q:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23492606.html?cid=238#a21807758


Sadly ...

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

returns 1    :-(
mx

0
 
shrimpforkAuthor Commented:
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.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
But what about that missing 0 issue  ?
0
 
shrimpforkAuthor Commented:
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.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I don't think [A-Z] will fly ....
0
 
Patrick MatthewsCommented:
Right, which leads us back to regular expressions...

:)
0
 
shrimpforkAuthor Commented:
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?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
No
0
 
Patrick MatthewsCommented:
SQL Server would support this, though...
0
 
shrimpforkAuthor Commented:
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 :)
0
 
rheitzmanCommented:
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))
0
 
shrimpforkAuthor Commented:
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
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23492830.html
and
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23491436.html
0
 
OnALearningCurveCommented:
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.
0
 
shrimpforkAuthor Commented:
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 http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23491436.html it was painful to watch.
0
 
OnALearningCurveCommented:
Ahhhh,

I see,

Sorry to waste your time.

Mark.
0
 
shrimpforkAuthor Commented:
No waste...
0
 
OnALearningCurveCommented:
OK,

Not being one to know when to give up, how about this:

define first field (SET):

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

Then use:

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

CHOPS should return the position of the first (Non Numeric Character).

How did I do?

Mark.
0
 
shrimpforkAuthor Commented:
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.
0
 
OnALearningCurveCommented:
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
0
 
rheitzmanCommented:
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

Open in new window

db6.mdb
0
 
Patrick MatthewsCommented:
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 http://www.experts-exchange.com/Q_23491436.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
0
 
shrimpforkAuthor Commented:
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.
0
 
Patrick MatthewsCommented:
Mark,

I am not worthy :)

Regards,

Patrick
0
 
OnALearningCurveCommented:
Patrick,

I am honoured.

Mark.
0
 
OnALearningCurveCommented:
shrimpfork,

Glad I could help.

:)

Mark.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 10
  • 6
  • 6
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now