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
