Wednesday, January 6

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 gives you. (e.g., 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!


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


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

Thanks Patrick!

Complete T-SQL code for encoding:

@idToShorten numeric(18,0),
@shortenedID varchar(20) OUT

SET @shortenedID = 'NULL ID'

IF @idToShorten < 1

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

DECLARE @shortened varchar(20) = ''

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

SELECT @shortenedID = @shortened

Complete T-SQL code for decoding:

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

SET @idExploded = -1

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

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)
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

SET @idExploded = @exploded