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
Just trying to make it all work... and then stay that way 