Amazon Redshift: How can I convert a 15 char Salesforce Id value into an 18 char Id value… here is a solution to the problem.
Amazon Redshift: How can I convert a 15 char Salesforce Id value into an 18 char Id value
I keep my SalesForce data in the Amazon Redshift database.
I want to create a function on Redshift that converts a SalesForce 15-character ID to an 18-character ID. I found this topic to guide how:
But none of these functions run on Redshift, and I can’t use it\ create similar functions on Amazon Redshift databases. (Have to say I’m new @this.)
Can someone provide code for Redshift?
Solution
Oh, good question!
I found some conversion codes 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 it to create Amazon Redshift User-Defined Functions (UDFs):
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:
SELECT f_salesforce_15_to_18 ('500A000000D34Xf')
It seems to work, but please test it!