Category Archives: SQL Server

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

Surprising SQL String comparison with untrimmed string

I’m a bit big-headed about my SQL abilities generally, and must admit that when it came to doing straight string equality comparisons in T-SQL I thought that LIKE and = were identical (ok, I should have really read BOL). Thus which of the following would you expect to return a row and which wouldn’t?

select 'Oh yeah' where 'a' = 'a  '           -- Statement 1

select 'Oh yeah' where 'a' = RTRIM('a  ')    -- Statement 2

select 'Oh yeah' where 'a' LIKE 'a  '        -- Statement 3

select 'Oh yeah' where 'a' LIKE RTRIM('a  ') -- Statement 4

-- 

select 'Oh yeah' where 'a' = 'a'             -- Statement 5

select 'Oh yeah' where 'a' LIKE 'a'          -- Statement 6

Give it a try, I’m not going to spoil it for you…I’ve included statements 5 & 6 so that you can look at the execution plan and relative costs, I never thought that LIKE could be less expensive than =. Learn some new SQL every day…

Scripting SQL Server diagrams to files for source control

We have recently started storing our SQL Server 2005 database schema and test data in a SubVersion repository but have an issue that diagrams are not supported by any of the commercial products out there that we’ve found.

I came across some code on CodeProject by Craig Dunn at http://www.codeproject.com/KB/database/ScriptDiagram2005.aspx. Which in turn was based on some SQL Server 2000 code by Clay Beatty.

However I found some issues with this, the first was that the output SQL script was piped to the SQL messages (e.g. PRINT rather than SELECT) which I couldn’t work out how to get at using .NET.  If anyone knows how to get at the messages I would be most interested!  The second issue was that it only worked for a single diagram per-call.

Thus I’ve tweaked it to output a two column table with the first column storing the diagram name and the second column the SQL script to generate the diagram.  The Stored Procedure can take a @name parameter if you wish to script a single diagram in which case you’ll only get one row in the resultset.

Included is a PowerShell script which then puts each row in to its own file which should be somewhat suitable for inclusion in Source Control.  I would like to say this is my first ever PowerShell script and all of this was done in a hurry.  Edit the PowerShell script to specify your database connection string and the path you want the SQL scripts placed in.  To run the PowerShell script you must allow unsigned code (http://www.microsoft.com/technet/scriptcenter/topics/winpsh/manual/run.mspx) e.g.

 Set-ExecutionPolicy RemoteSigned

Code is at: http://www.provenworks.com/Blogs/Joel/ScriptDBDiagrams.zip 

If you find it useful please comment below!