# How can I convert a 15 char Id value into an 18 char Id value?

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.

-
On what technology are you trying to calculate the 18 char IDs? ah, I see you! – Simon Lawrence Feb 12 '14 at 12:40

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'}
};
``````
-
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 '14 at 21:23
I've corrected the example. – Alex Tennant Jul 31 '14 at 9:11

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);
}
}
``````
-

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!

-
Thanks. I am working on a C# implementation now, will post it when I have it complete and tested. – Yaakov Ellis Feb 12 '14 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 '14 at 12:47
I just posted my C# implementation – Yaakov Ellis Feb 12 '14 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 '14 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;
``````
-

Python version

``````import string
import sys

bin_lookup = {
'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'
}

def sf_replace(incoming_id_char):

if incoming_id_char in string.ascii_uppercase:
return '1'
else:
return '0'

def expand_sf_id(incoming_sf_id):

if len(incoming_sf_id) != 15:
raise ValueError('id string must be exactly 15 characters long')

# split into list of 3 5-character chunks
id_chunks = map("".join, zip(*[iter(incoming_sf_id)]*5))

suffix = ''

for id_chunk in id_chunks:

# replace all capital letters with 1, non cap letters with 0
lookup_components = [sf_replace(id_char) for id_char in id_chunk]

# make it string and reverse it
lookup_chunk = "".join(lookup_components)[::-1]

# get the letter from the lookup table based on the reversed string
bin_replacement = bin_lookup[lookup_chunk]
suffix += bin_replacement

#add the suffix to the origional id
expanded_sf_id = incoming_sf_id + suffix

return expanded_sf_id

if __name__ == '__main__':
print expand_sf_id(sys.argv[1])
``````
-

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++) {
for (int j = 0; j < 5; j++) {
char x = id.charAt((i*5) + j);
if (Character.isUpperCase(x))
}
}
System.out.println("Converted value: " + id.toUpperCase());
}
}
``````
-

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"`

-
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 '14 at 21:19
In theory, yes but in practical use the amount of unique values is less. The full range is 62^15. First three characters designate which object it is, fourth character tells you which Salesforce instance the object belongs to (or the SF org owning the object rather, I'm not 100% sure, it's reserved at least) so that leaves 62^11 characters So 5.2 x 10^19 per object instead of 7.7 x 10^26. Should still be enough for anyone *famous last words* – Martin Peters Nov 21 '14 at 15:50

In the off chance somebody does the same thing we do and replicate Salesforce data to postgresql, you may find this postgres-function useful. I take no credit for the algorithm, I've simply adapted already listed algorithms to work in plpgsql.

``````CREATE OR REPLACE FUNCTION id15to18(inputid text)
RETURNS text AS
\$BODY\$
DECLARE flags INTEGER DEFAULT 0;
DECLARE suffix TEXT DEFAULT '';
DECLARE chr TEXT DEFAULT NULL;
BEGIN
IF char_length(inputid) != 15 THEN
RETURN inputid;
END IF;
FOR i IN 0 .. 2 LOOP
flags := 0;
FOR j IN 0 .. 4 LOOP
chr := substring(inputid FROM (i)*5+j+1 FOR 1);
IF ( ascii(chr) >= ascii('A') AND ascii(chr) <= ascii('Z') ) THEN
flags := flags + (1 << j);
END IF;
END LOOP;
suffix := suffix || substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ012345' FROM flags+1 FOR 1);
END LOOP;
RETURN inputid || suffix;
END;
\$BODY\$
LANGUAGE plpgsql IMMUTABLE
COST 100;
``````
-

Here is a PHP version of the conversion function:

``````function convert_sfid(\$sfid15) {
if (strlen(\$sfid15) != 15) {
return \$sfid15;
}

\$chunks = str_split(\$sfid15, 5);
\$extra = '';
foreach (\$chunks as \$chunk) {
\$chars = str_split(\$chunk, 1);
\$bits = '';
foreach (\$chars as \$char) {
\$bits .= (!is_numeric(\$char) && \$char == strtoupper(\$char)) ? '1' : '0';
}
\$map = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ012345';
\$extra .= substr(\$map, base_convert(strrev(\$bits), 2, 10), 1);
}

return \$sfid15 . \$extra;
}
``````
-

Here is an Objective-C Version:

``````/**
Converts SF 15 digit Id's to SF 18 digit Id's. 15 digit SFID rely's on case sensitivity for uniqueness, 18 digit does not.
@param fifteenDigitId NSString case sensitive version of SFID (15 digit)
@return caseInSensitive NSString 18 digit version of SFID
*/
+ (NSString*)convertToCaseSensitiveSFId:(NSString *)fifteenDigitId {
NSString *eighteenDigitId = nil;

// First check that we aren't trying to convert an already 18 digit Id.
if (fifteenDigitId.length == 18) {
DLog(@"SFID is already 18 digit ID");
eighteenDigitId = fifteenDigitId;
}
else {
NSString *suffix = @"";
for(int i = 0 ; i < 3; i++) {

NSInteger flags = 0;

for(int j = 0; j < 5; j++) {
NSRange range = NSMakeRange(i * 5 + j, 1);
NSString *c = [fifteenDigitId substringWithRange:range];

NSComparisonResult resultA = [c compare:@"A"];
NSComparisonResult resultZ = [c compare:@"Z"];

if ((resultA == NSOrderedDescending || resultA == NSOrderedSame) && (resultZ == NSOrderedAscending || resultZ == NSOrderedSame)) {
flags += 1 << j;
}
}

if (flags <= 25) {
NSRange flagRange = NSMakeRange(flags, 1);
suffix = [NSString stringWithFormat:@"%@%@", suffix, [@"ABCDEFGHIJKLMNOPQRSTUVWXYZ" substringWithRange:flagRange]];
}
else {
NSRange flagRange = NSMakeRange(flags - 26, 1);
suffix = [NSString stringWithFormat:@"%@%@", suffix, [@"012345" substringWithRange:flagRange]];
}
}

eighteenDigitId = [NSString stringWithFormat:@"%@%@", fifteenDigitId, suffix];
}

return eighteenDigitId;
}
``````
-

I ran across this web page that has an online tool to convert from 15 to 18 characters. I tested it with 1 ID and it was accurate. It's all done in a fairly short amount of JavaScript directly on the `<button>`.

``````<button onclick='javascript:if(document.getElementById("J").value==""){alert("Please enter into the left column a list of IDs on 15 chars");return;}var y=document.getElementById("J").value.split("\r").join("").split("\n");var z=new Array();for(var x=0;x<y.length;x++){if(y[x].length==15){var s="";for(var i=0;i<3; i++){var f=0;for(var j=0;j<5;j++){var c=y[x].charAt(i*5+j);if(c>="A" && c<="Z")f+=1<<j;}s+="ABCDEFGHIJKLMNOPQRSTUVWXYZ012345".charAt(f);}z.push(y[x]+s);}else{alert("Error : "+y[x]+" has not a length of 15 characters"+y[x].length);return;}}document.getElementById("L").value=z.join("\r\n");'>15 => 18</button>
``````
-

more concise (and likely faster) Java version:

``````private static char[] SF_ID_SUFFIX_LOOKUP = "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345".toCharArray();
public static String sfId15To18(final String sfId)
{
if (sfId.length() != 15) {
throw new IllegalArgumentException("Input Salesforce ID must be of length 15.");
}
final StringBuilder sb = new StringBuilder(18).append(sfId15);
for (int i = 0; i < 3; i++) {
for (int j = 0; j < 5; j++) {
if (Character.isUpperCase(sfId15.charAt(5 * i + j))) {
}
}
}
return sb.toString();
}
``````
-
My bold "faster" claim was intended relative to the original C# algorithm (which my unit testing showed to be about 3x slower than this one when implemented in Java). This one is likely somewhat faster than the first Java solution posted and very similar to the Java solution posted by Brian Hayes. – James Courtney Mar 24 at 19:04
During unit testing I realized I'd make a mistake in the original posting and wasn't reversing the segments of 5 characters for encoding. Now that that's fixed it's essentially identical to Brian Hayes solution - shoulda read further to his solution and saved some time this AM:) – James Courtney Mar 24 at 22:47

Somehow there is no Javascript version posted here. Let's remedy that.

``````(function (w) {
w.normalizeId = function (id) {
var i, j, flags, alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345",
isUppercase = function(c) {
return c >= "A" && c < = "Z";
};

if (id == null) return id;
id = id.replace(/\W/g, "");
if (id.length != 15) { return id; }

for (i = 0; i < 3; i++) {
flags = 0;
for (j = 0; j < 5; j++) {
if (isUppercase(id.charAt(i * 5 + j))) { flags += 1 << j; }
}
id += alphabet.charAt(flags);
}
return id;
}
})(window);
``````
-