+ Start a Discussion
astroastro 

Convert 15 digit ID to 18

Hey guys,

 

needed a way to convert from a 15 digit ID to an 18 digit one.

Found the javascript version for just such an action here: http://community.salesforce.com/sforce/board/message?board.id=general_development&message.id=13148

 

tried to make my own apex version but the last digit keeps coming out wrong.  Any Ideas?

 

Thank's

 

 

 

String id = '001Q0000002NXad'; //a sample id

 

String suffix = '';

for(integer i=0;i<3;i++){

Integer flags = 0;

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

if(c >= 'A' && c <='Z'){

flags += 1 << j;
}
}

if (flags <= 25) {

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

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

System.debug('15-DIGIT:'+Id);
System.debug('18-DIGIT:'+Id + suffix);

 

 

 

Message Edited by astro on 05-01-2009 10:04 PM
Message Edited by astro on 05-01-2009 10:10 PM
ShikibuShikibu

Did you have a look in salesforce help? It doesn't provide apex code, but it does have an algorithm in English. Search for "18 character ID", or click here.

 

There's also a python module in an open source project called pyax (written by my colleague), which you may find helpful. You can find pyax.sobject.util.id_15_to_18 at launchpad.

 

If you write working apex code, please do publish it back here; I'm sure many others would benefit.

ShikibuShikibu

astro, your code is in a code box, but it's all mangled anyway. I was having that problem, using Safari 3 on OSX, and found that if I switch to Firefox, it works.


I wonder if Salesforce has changed something recently that is causing a bad interaction with Safari, because I don't recall this happening last week (tho I was using Safari 4 last week).

BritishBoyinDCBritishBoyinDC

Here's the VBA macro from the connector that should help:

 

 

Function FixID(InID As String) As StringDim InChars As String, InI As Integer, InUpper As StringDim InCnt As IntegerInChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345"InUpper = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"InCnt = 0For InI = 15 To 1 Step -1 InCnt = 2 * InCnt + Sgn(InStr(1, InUpper, Mid(InID, InI, 1), vbBinaryCompare)) If InI Mod 5 = 1 Then FixID = Mid(InChars, InCnt + 1, 1) + FixID InCnt = 0 End If Next InI FixID = InID + FixIDEnd Function

 

 

 

BritishBoyinDCBritishBoyinDC

Let's try that in Firefox:

 

 

Function FixID(InID As String) As String If Len(InID) = 18 Then FixID = InID Exit Function End If Dim InChars As String, InI As Integer, InUpper As String Dim InCnt As Integer InChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345" InUpper = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" InCnt = 0 For InI = 15 To 1 Step -1 InCnt = 2 * InCnt + Sgn(InStr(1, InUpper, Mid(InID, InI, 1), vbBinaryCompare)) If InI Mod 5 = 1 Then FixID = Mid(InChars, InCnt + 1, 1) + FixID InCnt = 0 End If Next InI FixID = InID + FixID End Function

 

 

astroastro

thank's guys for all your responses, and code snippets, I will try and fix my code based on what snippets you gave me.

 

And I tried looking for the algorithm in the salesforce released materials but couldn't find it, I'll try again.

*just noticed the link you put up shikibu thank's for that. 

 

It's annoying me because it's just the last digit that is off lol

 

I'll definitely post it up on the code share when I get it going.

 

Thank's again

 

*also i re-posted the code again in ffox, sorry about it not being readable before.

 

 

Message Edited by astro on 05-01-2009 10:06 PM
Message Edited by astro on 05-01-2009 10:15 PM
astroastro

not sure of any way to do something such as this in salesforce which is why I don't think I can find it.

 

Salesforce treats lowercase and uppercase char's equally.  meaning they hold the same value, weather htat's ascii or not, i'm not sure.  Strings and Integer's do not mix in this world

 

 

I can't seem to find a way obtain the ascii value for a string.

 

Am I missing something here or can this not be done with apex?

 

my previous code was failing on the if( c >='A' && c <='Z') part because uppercase or lower it was always true

 

....... 

ShikibuShikibu
Have you tried using touppercase or tolowercase?
SuperfellSuperfell
Can you backup a step and explain why you need this ?
astroastro

shikibu: yes I have tried toUppercase toLowercase no difference sadly

 

sure I'll back up and explain the use case:

doing an integration from one system to another and one of the values being passed from the outside system are SFDC ID's.  They iwll be sending both 15 and 18 digit id's so I need a way of converting them all to 18 or 15 and having my logic to the rest of the work based on that standard.

 

Hope that helps.

 

also tried Integer.valueOf(''); but that only works for strings that contain numbers. 

ShikibuShikibu

In that case, you don't need to do anything.

 

 

ID id18 = '50020000003Oh54AAC'; ID id15 = '50020000003Oh54'; system.debug( id18 == id15 ); 20090502235841.353:AnonymousBlock: line 4, column 1: true

 

 

 

 

I believe that the extra three characters are check digits, a kind of error correction for systems (some Microsoft product?) that is case-insensitive, while Salesforce object IDs require upper and lower case in the first 15 characters.

 

In any case, as long as you declare the type as ID, rather than String, ids that refer to the same object will compare equal, whether they are 15 or 18 characters.

 

If this is inaccurate, I'm sure Simon will be able to provide a better answer.

astroastro

ahh excellent.  There was a particular reason why I moved all my ID's to Strings but I forget, I'll rework my code to fit with ID's and work around it since it's better than what I'm current ly doing, (checking the  string size and cutting it down to only 15 if it's 18 ..which actually may not work all the time if the cases are off.

 

Thank's for the insight and quick response I truly appreciate it! 

ShikibuShikibu
Make sure that whatever system is providing you with 18 characters will not fail if you give it back 15. (I think that so long as your external systems don't mess with the case of the IDs, they should be fine).
astroastro

Yes very good point, I have told them to be aware of this.

 

argh integrations :) 

SD_ChargersSD_Chargers

Hi Astro,

 

 Did you get find a solution to how to convert 15 digit IDs to 18?

 

SD

TaoDuhTaoDuh

I almost didn't believe it myself but this works.

 

    private String idsTo18(String initialVal) {
        if (initialVal.length() != 15) return initialVal;
        ID convertToLong = initialVal;
        String longId = convertToLong;
        return longId;
    }

 

My need for this function is when you build a SOQL query string for execution, the string is case insensitive.  If I have 15-character IDs I can get too many hits.

CAfromCACAfromCA

Standing on the shoulders of giants, I wrote this for the fun of it:

 

 

public static String ID15to18(String inID) {
  if (inID.length() == 18) { return inID; }
  else if (inID.length() != 15) { return ''; }
  else {
    String suffix = '';
    for (Integer i = 0; i < 3; i++) {
      Integer flags = 0;
      for (Integer j = 0; j < 5; j++) {
        String c = inID.substring(i*5+j, i*5+j+1);
        if ( ( c.compareTo('A') >= 0 ) && ( c.compareTo('Z') <= 0 ) ) {
          flags += 1 << j;
        }
      }
      suffix = suffix + 'ABCDEFGHIJKLMNOPQRSTUVWXYZ012345'.substring(flags, flags+1);
    }
    return inID + suffix;
  }
}

 I make no guarantees, but it seems to work in my quick-and-dirty tests.

 

ErikMittmeyerErikMittmeyer

Did you ever consider creating an according formula field instead? You can easily add this to reports and export case-insensitive IDs to any app that needs them (e.g. Excel or Access). Look up http://community.salesforce.com/t5/Apex-Code-Development/Formula-Field-calculating-the-18-digit-ID-from-the-15-digit-ID/td-p/189848 for details.

Hope this helps,
Erik

d19engeld19engel

Would it help if you could export the IDs into a Google Spreadsheet and convert them there?

 

Here is a help article describing how to convert 15 to 18 digit salesforce ids in google spreadsheets. It's designed to be very easy so any user can do it.

Nisse Knudsen.ax1026Nisse Knudsen.ax1026

Hehe, I like that solution! Works fine for me too! ;)

Ankur_ShuklaAnkur_Shukla

Hi - You can use this web page to do a quick a convert for a single record.

Thanks,

Ankur


http://ankurshukla-developer-edition.na12.force.com/CovertId/ConvertSalesforceID

colliecollie

If I download my data from Sales Force and make changes. How do I upload using Apex data loader to upload the revised data?

I need a quick and easy solution. thanks

Starz26Starz26

Why not just assing the ID to a string. Salesforce does the conversion for you:

 

String a = Account.ID;

 

Bam, a = the 18 chr id

Starz26Starz26

collie wrote:

If I download my data from Sales Force and make changes. How do I upload using Apex data loader to upload the revised data?

I need a quick and easy solution. thanks


Um, Just upsert the data back using dataloader

jwalshjwalsh

Pure java version, in case anyone ever needed it (props @astro and @AndyH47):

 

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

 

CLRGCLRG

In case anyone needs this inside sql server, which I did, here's the function I wrote:

 

USE [urdb]
GO
/****** Object:  UserDefinedFunction [dbo].[FX_convertIdTo18Digit]    Script Date: 01/07/2013 16:16:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER function [dbo].[FX_convertIdTo18Digit](@InID nvarchar(18)) returns NCHAR(18) 
AS
BEGIN
-- DECLARE @InID nchar(15)='xxxxxxxxxxxxxxx'
/*
to test run this script which should return 0 records
SELECT top 5000 ID,DBO.FX_convertIdTo18Digit(LEFT(id,15)) myid into #tmp
FROM urtable

select * from #tmp
where ID<>myid
*/
DECLARE @ACCTID NCHAR(18)=''
	if(LEN(@InID) = 18)		
		return @InID;
	
	DECLARE @Debug VARCHAR(4000)
	DECLARE @InChars NVARCHAR(32)='ABCDEFGHIJKLMNOPQRSTUVWXYZ012345'
	DECLARE @InUpper NVARCHAR(26)='ABCDEFGHIJKLMNOPQRSTUVWXYZ'
	DECLARE @InI int=15, @InCnt Int=0
	WHILE @InI>0
	BEGIN
		DECLARE @mid nchar(1)=SUBSTRING(@InID, @InI, 1)
		--PRINT 'FINDING MID ' + @mid
		
		--check to see if the current character can be found in inupper as you go one by one
		--comparing binary, if so, return the position found
		DECLARE @InUpperChars int=1
		DECLARE @InStrReturn int=0
		WHILE @InUpperChars<27
		BEGIN
			IF(CONVERT(binary,@mid)=CONVERT(binary,substring(@InUpper,@InUpperChars,1)))
			BEGIN
				SET @InStrReturn=1
				--PRINT 'FOUND ' + @mid + ' AT ' + CONVERT(VARCHAR(250),@InStrReturn)
				BREAK
			END		
			SET @InUpperChars=@InUpperChars+1	
		END
		SET @InCnt = 2 * @InCnt + @InStrReturn
		--PRINT 'InCnt after find is ' + CONVERT(varchar(250),@InCnt)		
		If (@InI % 5) = 1 
		BEGIN
		   SET @ACCTID = SUBSTRING(@InChars, @InCnt + 1, 1) + @ACCTID
		   --Print 'FixId ' + @ACCTID
		   SET @InCnt = 0
		END
		SET @InI=@InI-1
	END 
	SET @ACCTID=@InId+@ACCTID
	--PRINT @ACCTID
	RETURN @ACCTID
	
	
END    	

 

 

rforce2rforce2

Here is a javascript version, I use it on one of my utility pages, and it can be used from within the "Inspect Element" at any time:

 

function convertId(inId){
    if (inId == null)
         return inId;
    inId = inId.replace(/\"/g, "");
    if (inId.length != 15)
        return null;
    var sfx = "";
    for (var i = 0; i < 3; i++) {
        var pos = 0;
        for (var j = 0; j < 5; j++) {
            var c = inId.charAt(i * 5 + j);
            if (c >= "A" && c <= "Z")
                pos += 1 << j;
        }
        if (pos <= 25)
            sfx += "ABCDEFGHIJKLMNOPQRSTUVWXYZ".charAt(pos);
        else
            sfx += "012345".charAt(pos - 26);
    }
    return inId + sfx;
}

 nJoy

Scott CScott C

A proper SQL Server version should have set-based logic instead of a bunch of WHILE loops.  Here is my version.

 

-- Standardize Salesforce Ids
-- Extend case-sensitive 15-char Ids to case-insensitive 18-char
-- Only affects 15-char Ids, 18-char Ids are returned unmodified
CREATE FUNCTION [dbo].[SalesforceId18] ( @Id NCHAR(18) )
RETURNS NCHAR(18) AS BEGIN
    DECLARE @code NCHAR(32) = N'ABCDEFGHIJKLMNOPQRSTUVWXYZ012345' ;
    IF LEN(@Id) = 15
      SELECT @Id = LEFT(@Id, 15)
            + SUBSTRING(@code, 1 + SUM(CASE WHEN ASCII(SUBSTRING(@Id, pos, 1)) BETWEEN 65 AND 90 THEN val ELSE 0 END), 1)
            + SUBSTRING(@code, 1 + SUM(CASE WHEN ASCII(SUBSTRING(@Id, pos + 5, 1)) BETWEEN 65 AND 90 THEN val ELSE 0 END), 1)
            + SUBSTRING(@code, 1 + SUM(CASE WHEN ASCII(SUBSTRING(@Id, pos + 10, 1)) BETWEEN 65 AND 90 THEN val ELSE 0 END), 1)
      FROM (
         SELECT pos = 1, val = 1
         UNION ALL SELECT 2,2
         UNION ALL SELECT 3,4
         UNION ALL SELECT 4,8
         UNION ALL SELECT 5,16
      ) x ;
   RETURN @Id ;
END

 

rockslayerrockslayer

So, I know this is an old thread but I would thought I'd lend some quick insight on the original question...

 

As developers, we often times have a tendancy to make things a bit more complicated than they need to be. If you have a String that needs to represent an Id (18char proper), simply run:

Id properId = Id.valueOf(String s);

 Debug the properId and you will see that SFDC converts the String to 18char Id for you. You can then use String.valueOf() if you need to back-in to String primitive again.

BiswajeetBiswajeet
Hi Astro,
You can try this

String FifteenDigit = 'a0390000009ooJG';
Id EighteenDigit = FifteenDigit;      
RVM GlobalRedRVM GlobalRed
sorry if this code adds to confusion, but thought I'd share a modification of CLRG's SQL function that inputs a table with a 15 digit SFID and outputs 18 digit SFID to specified field

ALTER proc [dbo].[SF_15_to_18]
--Populates @SF18VarName on @tablename with 18 char SFID from @SF15VarName
@tablename varchar(250) ,
    @SF15VarName varchar(250),
    @SF18VarName varchar(250) 
    as

/*Example Call Up:
Execute [dbo].[SF_15_to_18]
@tablename = '[dbo].[CHC_Accounts_20140818_RVM]'
,@SF15VarName = '[Salesforce ID]'
,@SF18VarName = 'SF18'
*/

exec(
'select *
, CAST('''' AS varchar(1)) as  CharHolder
, CAST('''' AS varchar(18)) as  SF18
, 0 as  InCnt
, 0 as  InStrReturn
into #temp from ' + @tablename+

'
/*
alter table #temp
add CharHolder varchar(1)
,SF18 varchar(18)
,InCnt int
,InStrReturn int

update #temp
set CharHolder = '''', SF18 = '''', InCnt = 0, InStrReturn  = 0
*/
DECLARE @Debug VARCHAR(4000)

    DECLARE @InChars NVARCHAR(32)=''ABCDEFGHIJKLMNOPQRSTUVWXYZ012345''

    DECLARE @InUpper NVARCHAR(26)=''ABCDEFGHIJKLMNOPQRSTUVWXYZ''

    DECLARE @InI int=15

    WHILE @InI>0

    BEGIN

  update #temp
        set CharHolder = SUBSTRING([Salesforce id], @InI, 1)

  DECLARE @InUpperChars int=1

  update #temp
  set InStrReturn  = 0

    
   update #temp
   set InStrReturn = case when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,1,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,2,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,3,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,4,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,5,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,6,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,7,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,8,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,9,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,10,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,11,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,12,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,13,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,14,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,15,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,16,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,17,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,18,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,19,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,20,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,21,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,22,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,23,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,24,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,25,1)) then 1
         when CONVERT(binary,CharHolder)=CONVERT(binary,substring(@InUpper,26,1)) then 1 else InStrReturn end



  update #temp
  SET InCnt = 2 * InCnt + InStrReturn

  If (@InI % 5) = 1
        BEGIN

   update #temp
   SET SF18 = SUBSTRING(@InChars, InCnt + 1, 1) + SF18

   update #temp
   SET InCnt = 0

        END

set @InI = @InI - 1
end



alter table #temp
add sf15 varchar(15)

update #temp
set sf15 = '+ @SF15VarName+'

ALTER TABLE #temp
ALTER COLUMN sf15 VARCHAR(50) COLLATE Latin1_General_CI_AS

update #temp
SET SF18=SF15+SF18



update '+ @tablename+'
set '+@SF18VarName+' = b.SF18
from '+ @tablename+' a
inner join #temp b on (a.'+@SF15VarName+' = b.'+@SF15VarName+')


')
sunny522sunny522
Conersion of 15 digit to 18 digit is simple.
We just need to declare a variable of type Id and then assign the 15 digit number to that variable.

String s15DigitLeadId = '00Q0o00001OoyzD'; //Assume this is record Id of Lead of 15 digits
Id i18DigitId = s15DigitLeadId;
system.debug('Eight Digit Id:'+i18DigitId);
https://salesforceglobe4u.blogspot.com/2018/12/how-to-convert-15-digit-salesforce.html

 
Daniel Banker 11Daniel Banker 11
Teradata
REPLACE FUNCTION sfdc_15id_to_18id ( Id varchar(18))
RETURNS varchar(18)
     LANGUAGE SQL
     DETERMINISTIC
     CONTAINS SQL
     SPECIFIC sfdc_15id_to_18id
     CALLED ON NULL INPUT
     SQL SECURITY DEFINER
     COLLATION INVOKER
     INLINE TYPE 1
RETURN
    case 
    when char_length(Id) = 15
    	then
    	(
          LEFT(Id, 15)
          || SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ012345' from 
				(CASE WHEN ASCII(SUBSTRING(Id from 1 for 1)) BETWEEN 65 AND 90 THEN 1 ELSE 0 END)
            	+ (CASE WHEN ASCII(SUBSTRING(Id from 2 for 1)) BETWEEN 65 AND 90 THEN 2 ELSE 0 END)
            	+ (CASE WHEN ASCII(SUBSTRING(Id from 3 for 1)) BETWEEN 65 AND 90 THEN 4 ELSE 0 END)
            	+ (CASE WHEN ASCII(SUBSTRING(Id from 4 for 1)) BETWEEN 65 AND 90 THEN 8 ELSE 0 END)
            	+ (CASE WHEN ASCII(SUBSTRING(Id from 5 for 1)) BETWEEN 65 AND 90 THEN 16 ELSE 0 END)
            	+ 1
            	for 1)            			            		            			
          || SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ012345' from 
				(CASE WHEN ASCII(SUBSTRING(Id from 6 for 1)) BETWEEN 65 AND 90 THEN 1 ELSE 0 END)
            	+ (CASE WHEN ASCII(SUBSTRING(Id from 7 for 1)) BETWEEN 65 AND 90 THEN 2 ELSE 0 END)
            	+ (CASE WHEN ASCII(SUBSTRING(Id from 8 for 1)) BETWEEN 65 AND 90 THEN 4 ELSE 0 END)
            	+ (CASE WHEN ASCII(SUBSTRING(Id from 9 for 1)) BETWEEN 65 AND 90 THEN 8 ELSE 0 END)
            	+ (CASE WHEN ASCII(SUBSTRING(Id from 10 for 1)) BETWEEN 65 AND 90 THEN 16 ELSE 0 END)
            	+ 1
            	for 1)
          || SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ012345' from 
				(CASE WHEN ASCII(SUBSTRING(Id from 11 for 1)) BETWEEN 65 AND 90 THEN 1 ELSE 0 END)
            	+ (CASE WHEN ASCII(SUBSTRING(Id from 12 for 1)) BETWEEN 65 AND 90 THEN 2 ELSE 0 END)
            	+ (CASE WHEN ASCII(SUBSTRING(Id from 13 for 1)) BETWEEN 65 AND 90 THEN 4 ELSE 0 END)
            	+ (CASE WHEN ASCII(SUBSTRING(Id from 14 for 1)) BETWEEN 65 AND 90 THEN 8 ELSE 0 END)
            	+ (CASE WHEN ASCII(SUBSTRING(Id from 15 for 1)) BETWEEN 65 AND 90 THEN 16 ELSE 0 END)
            	+ 1
            	for 1)
           )
  	 else Id
  	 end
;