Splunk Search

How to sort alphanumeric values?

auaave
Communicator

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!

Tags (1)
0 Karma
1 Solution

cmerriman
Super Champion

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

View solution in original post

cmerriman
Super Champion

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

DalJeanis
SplunkTrust
SplunkTrust

@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".

cmerriman
Super Champion

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.

auaave
Communicator

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.

0 Karma

cmerriman
Super Champion

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.

0 Karma

auaave
Communicator

Awesome! I will definitely check the site.
Thanks a lot!

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...