Replace letters with numbers

[timrich66]

Hi all,

I have an alert that looks for a specific message that includes the record ID.

I would like to be able to create a numeric value for that ID that could be used to create a unique ID when raising a ServiceNow ticket.  Therefore, all alerts for the same record ID would write to the same SNow ticket.

The record ID is a string of 7 alphanumerics - e.g. abc4efg

I would like to be able to change "abc4efg" to "1234567".  Thus the number does not change, but the letters are all the equivalent 1-26 number.

The only constant is the length of the record ID which is 7 characters.

I've looked at many answers, sadly none provide exactly what I am looking for.

Is this something that could be achieved using SED?  

Thanks in advance.

[PickleRick · Accepted solution]

Why '"generate" if you can use the value directly?

| eval myID=tonumber(yourID,36)

 Unless you want to have 7 digits at most. In this case good luck avoiding colisions 😉

[timrich66]

Thanks @PickleRick , I had tried using  "eval myId=tonumber(recordId,26)" not realising I had to use base 36 to include 0-9.

I am using strftime on the date and combining the date and recordId to give me one SNow ID per day for each record.

This gives me the solution I require.

[cmtri]

Try this rex command out, you'll need to go up to z of course:

| makeresults 1
| eval your_field="abc4ef"
| rex mode=sed field=your_field "s/a/1/g s/b/2/g s/c/3/g s/e/5/g s/f/6/g"

[maciep]

if you're looking to do this in search, then something like this might work - wouldn't be surprised if there was a better way.  But essentially an array for each letter, then loop over the recordid and replace letters with associated index of that letter in the letters array

| stats count 
| eval recordId="abc4efg"
| eval mvRecordId = split(recordId,"")
| eval letters=split("|,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z",",")
| eval serviceNowId = mvmap(mvRecordId,if(mvfind(letters,lower(mvRecordId))>0,mvfind(letters,lower(mvRecordId)),mvRecordId))
| eval serviceNowId = mvjoin(serviceNowId,"")

[timrich66]

Thanks @maciep , unfortunately our install is still on v7.3.2 so I cannot use 'mvmap'.  I will have to push the provider for an upgrade 🙂

[ITWhisperer]

Based on @maciep  solution, here is one that doesn't use mvmap

| makeresults
| eval recordId="abc4efg"
| eval mvRecordId = split(recordId,"")
| eval letters=split("|,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z",",")
| eval serviceNowId=""
| foreach 1 2 3 4 5 6 7
    [| eval letter=mvindex(mvRecordId,<<FIELD>>-1)
    | eval serviceNowId=mvappend(serviceNowId,if(mvfind(letters,lower(letter))>0,tostring(mvfind(letters,lower(letter))),letter))]
| fields - letter letters mvRecordId
| eval serviceNowId = mvjoin(serviceNowId,"")

[PickleRick]

This solution is prone to collision.

How do you distinguish "akakaka" from "kakakaa"? Or "111aaa1" from "1a1aa1a"?

[ITWhisperer]

Correct - but the OP doesn't specify how this should be avoided. There are a couple of ways this might be done. The indexes could be prefixed with a leading zero or two, the values could be joined with zeroes or nines or some other digit.

[PickleRick]

Sure, he didn't. But taking into account his desired usage, collisions should be avoided. Otherwise there's no point in this conversion.

As I wrote before, tonumber() gives a very straightforward conversion .