Function to Convert 15char Salesforce.com IDs to 18char ones in T-SQL (SQL Server)

If you’ve ended up here then you’ve already experienced the pain that is the fact that internally Salesforce use 18char IDs for everything, unfortunately the user is always presented with 15char versions. The additional 3 characters on the 18char version allow it to be compared case insensitively and so is much safer for handling within for example SQL Server when using a case insensitive collation.

The code below is based on the description and Dave‘s Javascript version found in this forum post.

I hope you find it useful:

* Note updated 13 June 2010 following feedback from David Eisaiah Engel

Download the SQL script here.

-- =============================================
-- Author:        Joel Mansford
-- Create date:     Jan 2010
-- Description:    Converts 15char SF Ids to 18char ones
-- History:
--  June 2010 - issue with over-running and incorrectly producing ']' as a character with input such as '0033000000dGpDN'
-- =============================================
CREATE FUNCTION fnConvertToCaseInsensitiveId
( @InputId CHAR(15)
)
RETURNS CHAR(18)
AS
BEGIN
-- Hacky way to raise an error but it works and there’s no alternative!
DECLARE @ErrorStringReally INT

IF LEN(@InputId)<>15

SET @ErrorStringReally =‘Input Salesforce Id must be exactly 15 characters input was “’+ @InputId+‘”’

DECLARE @OutputId CHAR(18)

DECLARE @Hash VARCHAR(3)

SET @Hash =

DECLARE @Chunk tinyint

DECLARE @ThisChunk CHAR(5)

DECLARE @CharPos tinyint

DECLARE @ThisHashDigit tinyint

— Split string in to 3 chunks of 5chars

SET @Chunk = 1

WHILE @Chunk<=3

BEGIN

SELECT @ThisChunk = RIGHT(LEFT(@InputId,@Chunk*5),5)

SET @ThisHashDigit = 0

SET @CharPos = 1

— Iterate over the chunk

WHILE @CharPos<=5

BEGIN

IF ASCII(SUBSTRING(@ThisChunk,@CharPos,1)) BETWEEN 65 AND 90 — If Uppercase

— then add a binary ’1′ digit in the appropriate position, otherwise it’s still 0

SET @ThisHashDigit +=POWER(2,@CharPos1)

SET @CharPos+=1

END

IF @ThisHashDigit>=26

— Digits 0-9, minus 26 as SFDC have numbers come ‘after’ letters

SET @Hash +=CHAR(@ThisHashDigit+4826)

ELSE

— Letter ‘A’

SET @Hash +=CHAR(@ThisHashDigit+65)

SET @Chunk+=1

END

SET @OutputId = @InputId + @Hash

RETURN @OutputId

END

Advertisements

7 responses to “Function to Convert 15char Salesforce.com IDs to 18char ones in T-SQL (SQL Server)

  1. Pingback: 15 or 18 Character IDs in Salesforce.com – Do you know how useful unique IDs are to your development effort? « Astadia EMEA's Blog

  2. Very cool…

    Here is an open source Google Spreadsheets Script that is based on David Padbury’s conversion function.

    We made the installation so easy that you don’t have to be a programmer to make this one work:

    http://www.engeljournal.com/tools/convert-15-to-18-digit-salesforce-ids-with-google-spreadsheets/

  3. Pingback: SQL Server 15 to 18 Character Salesforce IDs « Salesforce Snippets

  4. Thank you for this. It was very helpful.

  5. Pingback: Hello CaseSafeID! | Force Collective

  6. This was a REAL life saver! Thank you 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s