Amazon Redshift: How can I convert a 15 char Salesforce Id value into an 18 char Id value

Question

I'm Holding SalesForce Data on Amazon Redshift DB. I would like to create a function on Redshift that will convert SalesForce 15 Char ID to 18 Char ID. I found this topic that gives a direction of how to:

salesforce id - How can I convert a 15 char Id value into an 18 char Id value? - Salesforce Stack Exchange

But non of this functions is working on Redshift and I cannot use that \ create a similar function on Amazon Redshift DB. (Have to say I'm pretty new @ this.

Can someone have a code that works on Redshift?

Answer 1

Ooh, nice question!

I found some conversion code here:

• Salesforce.com ID Converter allows you to convert 15 digit, case-sensitive IDs to an 18 digit, case-safe version for use with Salesforce.com records.
• sf15to18/sf15to18.py at master · mslabina/sf15to18

I used that to create an Amazon Redshift User-Defined Function (UDF):

CREATE OR REPLACE FUNCTION f_salesforce_15_to_18 (id varchar)
  RETURNS varchar
STABLE
AS $$

# Code comes from: https://gist.github.com/KorbenC/7356677

for i in xrange(0, 3):
  flags = 0

  for x in xrange(0,5):
    c = id[i*5+x]

    #add flag if c is uppercase
    if c.isupper():
      flags = flags + (1 << x)

  if flags <= 25:
    id += 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'[flags]
  else:
    id += '012345'[flags - 26]

return id

$$ LANGUAGE plpythonu;

Run it with:

SELECT f_salesforce_15_to_18 ('500A000000D34Xf')

It seems to work, but please test it!