Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

What is the formula for calculating the additional 3 characters needed to append to a 15 char Id in order to form its equivalent 18 char Id?

I have a situation where I need to compare 15 char Ids (uploaded by users) with their 18 char equivalents (stored in a local database), and it would be most efficient if I could convert the 15 char Ids to 18 chars before comparing (The 18 char Ids are PK fields in my local database, and while I could compare using a like query clause, it would be more efficient to convert to 18 chars and perform lookups based on those values).

The base algorithm would be great - even better is an C# implementation.

share|improve this question
    
On what technology are you trying to calculate the 18 char IDs? ah, I see you! –  Simon Lawrence Feb 12 at 12:40

6 Answers 6

up vote 13 down vote accepted

Explanation of the algorithm: This is based on the algorithm given here. The example below is using a made up salesforce 15 char Id 001A000010khO8J

  1. Separate the 15 char Id into 3 groups of 5 chars. You now have 3 strings (the triplet variable below): 001A0, 00010 and khO8J
  2. Reverse each string. The three strings are now 0A100, 01000 and J8Ohk
  3. In each string, convert all Uppercase chars to 1, all other chars to 0. The three strings are now 01000, 00000 and 10100.
  4. Look up the corresponding char in the BinaryIdLookup based. This gives us a suffix of IAU.
  5. The 3 chars generated (in order) are appended to the 15 char Id value, giving you an 18 char Id value of 001A000010khO8JIAU.

I have created an implementation of this in C# and have tested this on a number of real Salesforce Ids and it seems to do the job. Code is in this gist or below:

static string Convert15CharTo18CharId(string id)
{
    if (string.IsNullOrEmpty(id)) throw new ArgumentNullException("id");
    if (id.Length == 18) return id;
    if (id.Length != 15) throw 
       new ArgumentException("Illegal argument length. 15 char string expected.", "id");

    var triplet = new List<string> { id.Substring(0, 5), 
                                     id.Substring(5, 5), 
                                     id.Substring(10, 5) };
    var str = new StringBuilder(5);
    var suffix = string.Empty;
    foreach (var value in triplet)
    {
        str.Clear();
        var reverse = value.Reverse().ToList();
        reverse.ForEach(c => str.Append(Char.IsUpper(c) ? "1" : "0"));
        suffix += BinaryIdLookup[str.ToString()];
    }
    return id + suffix;
}

static readonly Dictionary<string, char> BinaryIdLookup = new Dictionary<string, char>
{
    {"00000", 'A'}, {"00001", 'B'}, {"00010", 'C'}, {"00011", 'D'}, {"00100", 'E'},
    {"00101", 'F'}, {"00110", 'G'}, {"00111", 'H'}, {"01000", 'I'}, {"01001", 'J'},
    {"01010", 'K'}, {"01011", 'L'}, {"01100", 'M'}, {"01101", 'N'}, {"01110", 'O'}, 
    {"01111", 'P'}, {"10000", 'Q'}, {"10001", 'R'}, {"10010", 'S'}, {"10011", 'T'}, 
    {"10100", 'U'}, {"10101", 'V'}, {"10110", 'W'}, {"10111", 'X'}, {"11000", 'Y'}, 
    {"11001", 'Z'}, {"11010", '0'}, {"11011", '1'}, {"11100", '2'}, {"11101", '3'}, 
    {"11110", '4'}, {"11111", '5'}
};
share|improve this answer
    
This code is correct, but the example is not. The 13th character in the provided Salesforce ID is an Oh, not a zero. This means the third triplet is actually J8Ohk, which converts to 10100, and thus a 'U'. The actual 18 digit ID is 001A000010khO8JIAU. –  Ben Gottlieb Jun 21 at 21:23
    
I've corrected the example. –  Alex Tennant Jul 31 at 9:11

Here is some information on the algorithm.

And here is a great write up on coding it.

This appears to be taken from here and has (would you believe it!) broken links to a C# example!

share|improve this answer
    
Thanks. I am working on a C# implementation now, will post it when I have it complete and tested. –  Yaakov Ellis Feb 12 at 12:45
    
Awesome, I think a good google about may dig up the target of the dead links for the C# example, but with those wicked graphic/samples I'd imagine you can knock something together. Would definitely be worth posting it if you do. The15/18 Character ID is a very common query. Glad my links could help. –  Simon Lawrence Feb 12 at 12:47
1  
I just posted my C# implementation –  Yaakov Ellis Feb 12 at 13:32
    
Please try to avoid just referencing users to other websites, we aim to maintain information within this site itself. –  Samuel De Rycke Feb 12 at 13:58

This is not something which I developed. Full credits to the developer: John McTurnan SUB-ROUTINE BY ITSELF (No Demo)

//-----------------------------------------------------------------------------------------------------------------------------------
//-----------------------------------------------------------------------------------------------------------------------------------
//-------- SUBROUTINE:  CONVERT SFDC 15 DIGIT KEY TO 18 -----------------------------------------------------------------------------
// Credits: 
// John McTurnan:  Conversion from to Qlikview scripting from Javascript (jmcturnan@gmail.com)
// Ron Hess:  author of the Javascript version (http://boards.developerforce.com/t5/user/viewprofilepage/user-id/198)
//-----------------------------------------------------------------------------------------------------------------------------------
//-----------------------------------------------------------------------------------------------------------------------------------
//-----------------------------------------------------------------------------------------------------------------------------------
Sub SFDC_ID_15_to_18 (v_SFDC_ID15) //


  Let v_SFDC_ID18 = v_SFDC_ID15;
  Let v_SFDC_ID15 = chr(39) & v_SFDC_ID15 & chr(39);
  Let v_Check_digit = chr(39) & 'ABCDEFGHIJKLMNOPQRSTUVWXYZ012345' & chr(39);
  Let v_LT = ORD('A');
  Let v_GT = ORD('Z');


IF len(v_SFDC_ID15) <> 17 THEN //======  Check if ID conforms to basic length.  Exit & Null if not OK.
  Let v_SFDC_ID18 = Null();
  Exit Sub;
EndIf;


  Set v_i = 1; 

   For v_i = 1 to 3

      Set v_X = 0;
      Let v_Block = chr(39) & Mid($(v_SFDC_ID15), 5 * ($(v_i) - 1) + 1, 5) & chr(39);
      v_j = 1;

      For v_j = 1 TO 5

         Let v_C = ORD(Mid($(v_Block), $(v_j), 1));


         IF v_LT <= v_C And v_C <= v_GT THEN
          Let v_X = $(v_X) + POW(2,($(v_j) - 1));
         EndIf;


      Next


      Let v_temp1 = Mid($(v_Check_digit), $(v_X) + 1, 1);

      Let v_SFDC_ID18 = v_SFDC_ID18 & v_temp1;

  Next


  Let v_SFDC_ID18 = chr(39) & v_SFDC_ID18 & chr(39);

EndSub;
share|improve this answer

Java version:

public class SalesforceIDConverter
{
    public static String convertID(String id)
    {
        if(id.length() == 18) return id;

        String suffix = "";
        for(int i=0;i<3;i++){

            Integer flags = 0;

            for(int j=0;j<5;j++){
                String c = id.substring(i*5+j,i*5+j+1);

                if(c.compareTo("A")  >= 0 && c.compareTo("Z") <= 0){

                    flags += 1 << j;
                }
            }

            if (flags <= 25) {

                suffix += "ABCDEFGHIJKLMNOPQRSTUVWXYZ".substring(flags,flags+1);

            }else suffix += "012345".substring(flags-26,flags-26+1);
        }

        return id+suffix;
    }

    public static void main(String[] args)
    {
        String id =  "001M0000009odAH";
        String convertedID = convertID(id);
        System.out.println("id: " + id + "; converts to: " + convertedID);
    }
}
share|improve this answer

Java again, bit more old school:

public class To18 {
    static final String UPPER_CHARS = "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345";

    public static void main(String[] args) {
        String id = args[0];
        if (id.length() != 15) {
            System.err.println("Need 15 character string");
            return;
        }

        for (int i = 0; i < 3; i++) {
            int mask = 0;
            for (int j = 0; j < 5; j++) {
                char x = id.charAt((i*5) + j);
                if (Character.isUpperCase(x))
                    mask += Math.pow(2, j);
            }
            id += UPPER_CHARS.charAt(mask);
        }
        System.out.println("Converted value: " + id.toUpperCase());
    }
}
share|improve this answer

For anyone working on the Grails framework, here's a Groovy solution:

def toSalesforceId18(String id) {  //converts salesforce id15 to id18
   def valueToCode = { ((it < 26 ? "A" : "0") as char) + it % 26 }
   def binaryToCode = { valueToCode(Integer.parseInt(it, 2)) as char }
   def tripletToBinary = { it.reverse().replaceAll(/[^A-Z]/, "0").replaceAll(/[^0]/, "1") }
   def threeCodes = { it.replaceAll(/(.....)/, { binaryToCode(tripletToBinary(it[0])) }) }
   return id?.size() == 15 ? id + threeCodes(id) : id
   }

Example:
toSalesforceId18("001C000000o4Ooi") --> "001C000000o4OoiIAE"

share|improve this answer
    
For the curious, there are over 768,000,000,000,000,000,000,000,000 unique values for id15 (the number is covered in a blog entry of mine). –  Dem Pilafian Aug 8 at 21:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.