Hi,
How can I sort the below alphanumeric values?
From To
ROBOT 1 ROBOT 1
ROBOT 10 ROBOT 2
ROBOT 2 ROBOT 3
ROBOT 3 ROBOT 4
ROBOT 4 ROBOT 5
ROBOT 5 ROBOT 6
ROBOT 6 ROBOT 7
ROBOT 7 ROBOT 8
ROBOT 8 ROBOT 9
ROBOT 9 ROBOT 10
Thanks!
I'm going to assume you want them sorted numerically by From based on your data?
you'll want to create a separate field that has just the numeric values, OR that puts the numeric values in front of the string but has leading zeros (001 ROBOT)
this will create a new field for the From numbers |rex field=From "(?<FromNum>\d+)"
and you can then sort from that field and then remove it if you wish using |sort FromNum |fields - FromNum
if you want to put the leading zero number in front of the string (my example below gives 3 digit numbers, so it would be 001...020...100..), you could try: |rex field=From "(?<FromText>\w+)\s(?<FromNum>\d+)" |eval FromNum=substr("000".FromNum,-3)|eval From=FromNum." ".FromText|sort FromNum|fields - FromNum
I'm going to assume you want them sorted numerically by From based on your data?
you'll want to create a separate field that has just the numeric values, OR that puts the numeric values in front of the string but has leading zeros (001 ROBOT)
this will create a new field for the From numbers |rex field=From "(?<FromNum>\d+)"
and you can then sort from that field and then remove it if you wish using |sort FromNum |fields - FromNum
if you want to put the leading zero number in front of the string (my example below gives 3 digit numbers, so it would be 001...020...100..), you could try: |rex field=From "(?<FromText>\w+)\s(?<FromNum>\d+)" |eval FromNum=substr("000".FromNum,-3)|eval From=FromNum." ".FromText|sort FromNum|fields - FromNum
@cmerriman - Plus for good answer. Also note that the number doesn't have to be on the front. Sort works just as well with "Robot 001".
That's a good point. Though, with numbers in front, it would sort "001 AAA", "001 AAB", "002 AAA"... and with them at the end it would sort "AAA 001", "AAA 002", "AAB 001".... just depends what is more important in this case, alphanumeric sorting or numericalpha sorting.
Thanks a lot for your help! I have successfully sorted the alphanumeric field by copying/pasting the expression.
I don't have past experience of writing expressions and I am having trouble understanding what does the expressions means. Would you mind explaining what "(?\d+)" states? Sorry for being such a noob.
It's alright. The <...>
creates a naming group (like a field name) and the \d+
looks for any digit. \w+
is any non-digit. If you go to regex101.com you can type in the string you need to parse and test regex statements and it will explain what it's doing. It's a great resource.
Awesome! I will definitely check the site.
Thanks a lot!