shrimpfork
asked on
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.
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.
ASKER
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
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(va l(YourStri ng)))+1,1) )
BUT ... this will probably have the same issue with "12D01" etc that Val did in your other Q's
So, generic:
Instr(1,YourString, mid(YourString,Len(cstr(va
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
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(
returns 1 :-(
mx
ASKER
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.
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 ?
ASKER
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(PartNumbe r),[A-Z]))
I just don't know if I can search for any letter A thru Z in the Instr() function.
Right(PartNumber, Instr(StrReverse(PartNumbe
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...
:)
:)
ASKER
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...
ASKER
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(va l(YourStri ng)))+1,1) )
This may deal with the leading zero(s) issue:
Instr(1,YourString, mid(YourString,Len(cstr(va l("1" & YourString))),1))
This may deal with the leading zero(s) issue:
Instr(1,YourString, mid(YourString,Len(cstr(va
ASKER
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
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.
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.
ASKER
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.
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.
I see,
Sorry to waste your time.
Mark.
ASKER
No waste...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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((R eplace((Re place((Rep lace((Repl ace((Repla ce([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([S ET]))-(Len (LTrim([SE T])))+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]-[CH SP]+1))
Gives you the non numeric characters
SRT3: Int(Right([F],(Len([F])-[C HFP])))
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((R eplace((Re place((Rep lace((Repl ace((Repla ce([F],0," ")),1," ")),2," ")),3," ")),4," ")),5," ")),6," ")),7," ")),8," ")),9," ") AS [SET], IIf((Len(LTrim([SET])))=0, 0,((Len([S ET]))-(Len (LTrim([SE T])))+1)) AS CHSP, Len(RTrim([SET])) AS CHFP, Int(Left([F],[CHSP]-1)) AS SRT1, Mid([F],[CHSP],([CHFP]-[CH SP]+1)) AS SRT2, Int(Right([F],(Len([F])-[C HFP]))) 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
SET: Replace((Replace((Replace(
Gives you the string with digits replaced by spaces
CHSP: IIf((Len(LTrim([SET])))=0,
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]-[CH
Gives you the non numeric characters
SRT3: Int(Right([F],(Len([F])-[C
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(
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.
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
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
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
ASKER
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.
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
I am not worthy :)
Regards,
Patrick
Patrick,
I am honoured.
Mark.
I am honoured.
Mark.
shrimpfork,
Glad I could help.
:)
Mark.
Glad I could help.
:)
Mark.
? Instr(1,"123Xnb", mid("123Xnb",Len(cstr(val(
returns 4
but I have not fully tested this ...
mx