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,@CharPos–1)

SET @CharPos+=1

END

IF @ThisHashDigit>=26

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

SET @Hash +=CHAR(@ThisHashDigit+48–26)

ELSE

— Letter ‘A’

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

SET @Chunk+=1

END

SET @OutputId = @InputId + @Hash

RETURN @OutputId

END