Convert Salesforce IDs from 15 digit to 18 digit using sql code/function

Question

We are currently pulling in data from SalesForce to SQL Database tables. There are 2 custom fields on different objects that were created for the Lead ID and a look up for which event/task is linked (this can be an account id, contact id, or lead id). Both of these are pulling over the 15 digit ID.

I am trying to find out if there is any SQL code or a SQL function that will allow me to convert that 15 digit to an 18 digit ID.

I need to have that 18 digit ID to join back to the other objects.

We have already tried using the CASESAFEID(Id) function in SalesForce, but with the API that was already set up and the visibility levels our particular ETL is not showing that field. Also, we would need to get a consultant to mess with the look up column.

I would like to take the 15 digit ID and convert it to the 18 digit code. If the SalesforceID is 0035000002tAzbu, how do I get the converted 18 digit value to be 0035000002tAzbuACC. I need to get that last 3 digits using SQL query or SQL function.

Can you show some examples of how the 15 digit value should be converted to an 18 digit value - we don't know what conversion works with your system. — Dale K, Apr 29 '19 at 20:36
This Id would show as 0035000002tAzbu in the cell, but should be 0035000002tAzbuAAC. Is that what you mean? — Erin, Apr 29 '19 at 20:57
Yes, but edit it into your question rather than adding it as a comment. So what is the rule begin used to convert? How does one know to add 'AAC' at the end? — Dale K, Apr 29 '19 at 21:01
On a sidenote you will get better responses if you use the salesforce.stackexchange.com site — TemporaryFix, May 01 '19 at 19:26
Even though I am looking for SQL code to get the 18 digit it would be better to go onto the salesforce.stackexchange.com site? — Erin, May 02 '19 at 04:19
Maybe I misinterpreted your question, but it seemed like you were asking for the algorithm to produce the final 3 characters. if that is the case then that would be a better suited area to ask the question. It can be confusing — TemporaryFix, May 02 '19 at 16:22
No, I was looking for any SQL code that would add the 3 digits in a table. We are using SSIS to pull the data from Salesforce into our Data Warehouse, but there are 2 columns inside Salesforce that only have the 15 digit when it comes into the table. I wanted to know if there was any possible SQL code that would pull those into the table vs doing changes within Salesforce. — Erin, May 09 '19 at 18:11

score 0 · Answer 1 · answered Oct 02 '20 at 01:22

you could write a custom function in your sql database.

e.g. in snowflake, you can make a function like this


CREATE OR REPLACE FUNCTION dw.my_schema.f_sfdc_ch15_to_ch18("txt" string)
    RETURNS string
    LANGUAGE JAVASCRIPT
    AS '
    if ( txt == undefined || txt == "" || typeof txt == "undefined" || txt == null) {
        return ;
    } else {
        var id15, id18;
        
        if (txt.length == 18) {
            return txt;
        } else if (txt.length == 15) {
            id15 = [txt.trim()];
        } else {
            return "";
        }
        for ( var x=0; x < id15.length; x++ ) {
            var s = "";
            for ( var i=0; i<3; i++) {
                var f = 0;
                for (var j=0; j<5; j++) {
                    var c = id15[x].charAt(i*5+j);
                    if (c>="A" && c<="Z") {
                        f+=1<<j;
                    }
                }
                s += "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345".charAt(f);
            }
            id18 = id15[x]+s;
        }
    }
    return id18.toString();
    ';

and use it like this

select dw.my_schema.f_sfdc_ch15_to_ch18(id15) from mytable;

score -1 · Answer 2 · answered May 01 '19 at 19:25

-1

This value can be computed. Check out this flowchart.

Source: https://stackoverflow.com/a/29299786/3135974

answered May 01 '19 at 19:25

TemporaryFix

2,008
3
30
54

Convert Salesforce IDs from 15 digit to 18 digit using sql code/function

2 Answers2

Linked