Solved

Search string for first alpha character within a query

Posted on 2008-06-17
29
2,699 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
  • 6
  • +2
29 Comments
 
LVL 75
ID: 21807729
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
ID: 21807749
I'm not following you on this one.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 21807768
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 75
ID: 21807775
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
ID: 21807787
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
ID: 21807843
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
ID: 21807867
But what about that missing 0 issue  ?
0
 

Author Comment

by:shrimpfork
ID: 21807925
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
ID: 21807973
I don't think [A-Z] will fly ....
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 21807983
Right, which leads us back to regular expressions...

:)
0
 

Author Comment

by:shrimpfork
ID: 21807996
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
ID: 21808005
No
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 21808013
SQL Server would support this, though...
0
 

Author Comment

by:shrimpfork
ID: 21808122
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
 
LVL 7

Expert Comment

by:rheitzman
ID: 21808540
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
ID: 21808633
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
ID: 21811015
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
ID: 21812393
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
ID: 21812707
Ahhhh,

I see,

Sorry to waste your time.

Mark.
0
 

Author Comment

by:shrimpfork
ID: 21812765
No waste...
0
 
LVL 10

Accepted Solution

by:
OnALearningCurve earned 250 total points
ID: 21813875
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
ID: 21814695
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
ID: 21815671
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
ID: 21817295
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
ID: 21817548
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
ID: 31468155
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
ID: 21817663
Mark,

I am not worthy :)

Regards,

Patrick
0
 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 21817781
Patrick,

I am honoured.

Mark.
0
 
LVL 10

Expert Comment

by:OnALearningCurve
ID: 21819994
shrimpfork,

Glad I could help.

:)

Mark.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

739 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