Solved

Search string for first alpha character within a query

Posted on 2008-06-17
29
2,622 Views
Last Modified: 2011-10-19
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
Comment
Question by:shrimpfork
  • 10
  • 6
  • 6
  • +2
29 Comments
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
 

Author Comment

by:shrimpfork
Comment Utility
I'm not following you on this one.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
 

Author Comment

by:shrimpfork
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
But what about that missing 0 issue  ?
0
 

Author Comment

by:shrimpfork
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
I don't think [A-Z] will fly ....
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
Right, which leads us back to regular expressions...

:)
0
 

Author Comment

by:shrimpfork
Comment Utility
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
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
No
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
SQL Server would support this, though...
0
 

Author Comment

by:shrimpfork
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 7

Expert Comment

by:rheitzman
Comment Utility
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
 

Author Comment

by:shrimpfork
Comment Utility
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
 
LVL 10

Expert Comment

by:OnALearningCurve
Comment Utility
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
 

Author Comment

by:shrimpfork
Comment Utility
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
 
LVL 10

Expert Comment

by:OnALearningCurve
Comment Utility
Ahhhh,

I see,

Sorry to waste your time.

Mark.
0
 

Author Comment

by:shrimpfork
Comment Utility
No waste...
0
 
LVL 10

Accepted Solution

by:
OnALearningCurve earned 250 total points
Comment Utility
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
 

Author Comment

by:shrimpfork
Comment Utility
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
 
LVL 10

Expert Comment

by:OnALearningCurve
Comment Utility
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
 
LVL 7

Expert Comment

by:rheitzman
Comment Utility
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
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
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
 

Author Closing Comment

by:shrimpfork
Comment Utility
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
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
Mark,

I am not worthy :)

Regards,

Patrick
0
 
LVL 10

Expert Comment

by:OnALearningCurve
Comment Utility
Patrick,

I am honoured.

Mark.
0
 
LVL 10

Expert Comment

by:OnALearningCurve
Comment Utility
shrimpfork,

Glad I could help.

:)

Mark.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

728 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now