Function to Convert 15char 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)
-- 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)


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


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

SET @ThisHashDigit = 0

SET @CharPos = 1

— Iterate over the chunk

WHILE @CharPos<=5


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


IF @ThisHashDigit>=26

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

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


— Letter ‘A’

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

SET @Chunk+=1


SET @OutputId = @InputId + @Hash

RETURN @OutputId



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

  1. Pingback: 15 or 18 Character IDs in – 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:

  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: Logo

You are commenting using your 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 )

Connecting to %s