Custom URL Shortener/ID Encoder in Transact-SQL (T-SQL)

Recently I've been working on some public-facing web applications where I needed to expose non-sequential IDs to end users in email as both text and links. In a corporate intranet, or even client-serving extranet, environments, with help desk & administrative support available, I'd probably just use GUIDs all over the place, and let the help desk field the aggravation and educate the users on the cutting and pasting of, and pronouncing, GUID. (I'm evil, I know.)

Now in situations where user aggravation directly and/or substantially affects revenue or productivity, and the ability to leverage help desk type support is at a minimum, I decided to start encoding IDs, not unlike the hash a URL shortener like bit.ly gives you. (e.g. bit.ly/5tB2x5, where 5tB2x5 is your encoded or hashed value.)

I found a fairly simple to implement algorithm on Jonathan Snook's site. I initially encoded, or compressed, IDs in a C# port of Snook's work, and stored the result in SQL Server, as I had a bug in my decompression/decoding method and needed to lookup the values up.

I then found myself with a need to create these encoded ID values inside of stored procedures. I ported the work once again, this time to T-SQL. Again, encoding worked, but decoding failed. I was stymied until the keen eye of Patrick Muldoon, via Tech Valley Ruby Brigade, noticed a character set issue -- the decoding routine was not making the distinction between capital and non-capital letters, resulting in bad decoding. COLLATE to the rescue!

bad:

SET @currPos = CHARINDEX(@currentCharacter, @BigString)

good:

SET @currPos = CHARINDEX(@currentCharacter, @BigString COLLATE Latin1_General_CS_AS)

Thanks Patrick!

Complete T-SQL code for encoding:

CREATE PROCEDURE [dbo].[ShortenID]
 @idToShorten numeric(18,0),
 @shortenedID varchar(20) OUT
AS

BEGIN
 SET @shortenedID = 'NULL ID'

 IF @idToShorten < 1
  RETURN

 DECLARE @ShortChars varchar(100) = '8A2aBb95CcDdeFfGgHhJjK6kLMm3NnOPpQqRrSsTtUuV7vXxYyZ4z'
 DECLARE @charBase int = LEN(@ShortChars)
 DECLARE @currentCharacter char

 DECLARE @shortened varchar(20) = ''

 WHILE @idToShorten > 0
 BEGIN
  SET @currentCharacter = SUBSTRING(@ShortChars, (@idToShorten % @charBase), 1)
  SELECT @shortened = @currentCharacter + @shortened
  SET @idToShorten = FLOOR(@idToShorten/@charBase)
 END
 
 SELECT @shortenedID = @shortened
END

Complete T-SQL code for decoding:

CREATE PROCEDURE [dbo].[ExplodeShortID]
 @shortenedID varchar(20),
 @idExploded numeric(18,0) OUT
AS

BEGIN
 SET @idExploded = -1

 IF LTRIM(RTRIM(@shortenedID)) = ''
  RETURN

 DECLARE @ShortChars varchar(100) = '8A2aBb95CcDdeFfGgHhJjK6kLMm3NnOPpQqRrSsTtUuV7vXxYyZ4z'
 DECLARE @charBase int = LEN(@ShortChars)
 DECLARE @currentCharacter char
 DECLARE @currPos int = 0
 
 DECLARE @exploded numeric (18, 0) = 0

 DECLARE @lenShortenedID int = LEN(@shortenedID)
 DECLARE @i int = @lenShortenedID
 DECLARE @pow int = 0;
 DECLARE @someInt int = 0

 WHILE (@i > 0)
 BEGIN
  SET @someInt = (-1 * ( @i - LEN(@shortenedID) ))
  SET @currentCharacter = SUBSTRING( @shortenedID, @someInt+1, 1 )
  SET @currPos = CHARINDEX( @currentCharacter, @ShortChars COLLATE Latin1_General_CS_AS)
  SET @pow = POWER(@charBase, @i-1)
  SET @exploded = @exploded + (@currPos * @pow)
  
  SET @i = @i - 1
 END 
 
 SET @idExploded = @exploded
END