Sort records by character after number

[Rich Hartnett]

I have a database of ham radio call signs. Each call sign has a number in it, but the location varies. (It's always the second or third digit) I need the report to display the call signs alphabetically, but starting with the letter that comes after the number.

Here's an example of the first set of call signs, and order they should be in:

NI3A
KZ3AB
W3ABC
K3ACT
N3ADF
N3ADY
W4BE
W3JPR
WB5LPN

I assumed val(<yourfield>) would be the correct expression, but I don't know how to "point" the "yourfield" value to the letter that follows the number.

Rich
WA3RTH

[csda1]

Re: Sort records by character after number

Hi Rich,

....I assumed val(<yourfield>) would be the correct expression, but I don't know how to "point" the "yourfield" value to the letter that follows the number.

Do you understand what VAL() does? It returns the value of the leading numbers of a string passed to it. So unless you point at a number, your value will be 0. If you point to the letters after the number, the value will be 0.

This problem could be attacked by using a UDF function or a variety of complex expressions to separate the text, but is most easily solved by using regular expressions (which are not easy to specify). Below is an example to address your needs

data=<<%text%
NI3A
KZ3AB
W3ABC
K3ACT
N3ADF
N3ADY
W4BE
W3JPR
WB5LPN
%text%
' regex expression that returns 3 parts reorder and as a CSV
' - this could be sort or order expression to process all sections
?regex_grep(data,"^([a-z]+)([0-9]+)([a-z0-9]*)","\3,\1,\2"+crlf(),"I")
= A,NI,3
AB,KZ,3
ABC,W,3
ACT,K,3
ADF,N,3
ADY,N,3
BE,W,4
JPR,W,3
LPN,WB,5
' regex expression to return the characters after the numbers
' - this would be sort or order expression
?regex_grep(data,"^([a-z]+)([0-9]+)([a-z0-9]*)","\3"+crlf(),"I")
= A
AB
ABC
ACT
ADF
ADY
BE
JPR
LPN
' This returns the trailing letters for just one line of text with no CRLF
?regex_grep("NI3A","^([a-z]+)([0-9]+)([a-z0-9]*)","\3","I")
= "A"

[Stan Mathews]

Re: Sort records by character after number

I would add a calculated field to the table with the expression

IF(ISDIGIT(SUBSTR(CALL,2,1)),SUBSTR(CALL,3),SUBSTR(CALL,4))

where call is the field holding the callsign. Then sort on this new field taking advantage of the stated fact that the number will be in either the second or third positions.

Boy I wish I understood regular expressions like Ira.

Come to think of it, just sort on that expression.

Not sure what order

Z3ADF
N3ADF

should be in or if it matters.

This expression seems to order those as well.

IF(ISDIGIT(SUBSTR(CALL,2,1)),SUBSTR(CALL,3)+left(call,1),SUBSTR(CALL,4)+left(call,2))

[csda1]

Re: Sort records by character after number

Boy I wish I understood regular expressions like Ira.

Boy I wish I understood regular expressions like Ira wishes he could! :D

[G Gabriel]

Re: Sort records by character after number

You could simplify this to:

x="KZ3AB"
?word(regex_split(x,"([A-Z]+)"),2,crlf())
= "AB"

[Rich Hartnett]

Re: Sort records by character after number

Gabe that works like a champ!

Thank you, and thanks Ira and Stan for the other suggestions.

Rich

[Rich Hartnett]

Re: Sort records by character after number

Actually I stand corrected. As it turns out, Gabe's formula did not work correctly. I thought it had, but in the end, some of the call signs were not in correct alphabetical order.

I ended up using Stan's method:

IF(ISDIGIT(SUBSTR(CALL,2,1)),SUBSTR(CALL,3),SUBSTR(CALL,4))

and it worked fine. Again, thanks everyone for the suggestions.

[G Gabriel]

Re: Sort records by character after number

some of the call signs were not in correct alphabetical order.

They must be in a different format.
The expression will work just fine for the format you provided but a slight change in the format will not work. For instance, if you have more than one numeral or no numerals then it won't work. It will require modification of the expression but if you test it on any of the data you provided, it works just fine.
And you could even try a more simplified expression:

x="NI3A"
?tagged_pattern("1[0-9]2","2",x)
= "A"
x="KZ3AB"
?tagged_pattern("1[0-9]2","2",x)
= "AB"

Assuming the data are consistent.

[CALocklin]

Re: Sort records by character after number

Rich, why isn't WA8RZA in your list?:D

[Rich Hartnett]

Re: Sort records by character after number

Because you haven't send in your membership dues to the Green Mountain Repeater Association for this year yet.

What are you waiting for? (LOL)

www.gmra-md.org
(I'm the webmaster)


(Glad to see a fellow ticket-holder on this fourm.)

[Steve Workings]

Re: Sort records by character after number

I'll always remember the call sign my grandpa had on his cruiser on Lake Michigan. He'd get drunk and continually shout out WZZ5522!