Monthly Archives: January 2010

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


Greasemonkey Script to help filter Setup screen

I have real pains with the Setup screen within, I think it’s simply evolved to where it is today and as a result it is far from intuitive. For example ‘Packages’ live under the ‘Create’ menu but ‘Apex Classes’ are under ‘Develop’ – I don’t think I’ll ever get it!

However I’ve just stumbled across this excellent “Setup Enhancer for Salesforce” which is a Greasemonkey script for Firefox. For those who don’t know Greasemonkey is an addon for Firefox which is installed quickly and simply using the Addons menu within Firefox.

Once that’s installed visit and click the Install button, you should see a warning and then that’s it. Now logon to and go to setup and you should see a filter box in the top-left corner:

Now, as you type in to the box you should see the left-hand menu filter down:

Kudos to ‘setupscripter‘ for something which is going to make my life a little easier.

IndustryComplete is AppExchange App of the Week

Our latest product for, IndustryComplete has been named App of the Week, read more about it here, and here.

IndustryComplete provides a user interface to quickly look up Industry Codes (SIC / NAICS / ISIC ) and for an administrator to map each code to a small list of Industries / vertical markets. This allows consistent classification of accounts within leading to better segmentation.

If you require any custom development of IndustryComplete or CountryComplete don’t hesitate to contact us.