Update: Custom URL Shortener

This is an update to a previous piece on shortening a URL/encoding an ID.

TSQL CHARINDEX starting at position zero returns an unexpected value of ' '. TSQL updated to do some explicit swaps around position 0, as well as to better randomize the encoding characterset.

ShortenID:

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

BEGIN
 SET @shortenedID = 'NULL ID'

 IF @idToShorten < 1
  RETURN

 DECLARE @i int = 0
 DECLARE @ShortChars varchar(100) = 'TrCn8A2faBpb95ceDYdFhGjK6kmL3NxORPqSUtV7XvyH4zuQMsgJ'
 DECLARE @charBase int = LEN(@ShortChars)
 DECLARE @currentCharacter char
 DECLARE @currPos int

 DECLARE @shortened varchar(20) = ''

 WHILE @idToShorten > 0
 BEGIN
  IF @debug = 1 
  BEGIN
   PRINT CONVERT(varchar(20), @i) + ': @idToShorten: ' + CONVERT(varchar(20), @idToShorten)
  END
  
  SET @currPos = (@idToShorten % @charBase)
  IF @debug = 1 
  BEGIN
   PRINT '@currPos: ' + CONVERT(varchar(20), @currPos)
  END
  
  SET @currentCharacter = SUBSTRING(@ShortChars, @currPos, 1)
  IF @debug = 1 
  BEGIN
   PRINT '@currentCharacter: ' + CONVERT(varchar(20), @currentCharacter)
  END
  
  IF @currentCharacter = ' '
  BEGIN
   SET @currentCharacter = 'Z'
  END
  
  SELECT @shortened = @currentCharacter + @shortened
  IF @debug = 1 
  BEGIN
   PRINT '@shortened: ' + CONVERT(varchar(20), @shortened)
  END
  
  SET @idToShorten = FLOOR(@idToShorten/@charBase)
  SET @i = @i + 1
 END
 
 SELECT @shortenedID = @shortened
END

ExplodeID

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

BEGIN
 SET @idExploded = -1

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

 DECLARE @ShortChars varchar(100) = 'TrCn8A2faBpb95ceDYdFhGjK6kmL3NxORPqSUtV7XvyH4zuQMsgJ'
 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 @currPosSource int = 0

 WHILE (@i > 0)
 BEGIN
  IF @debug = 1 
  BEGIN
   PRINT '@i: ' + CONVERT(varchar(20), @i)
  END
  
  SET @currPosSource = (-1 * ( @i - LEN(@shortenedID) ))
  IF @debug = 1 
  BEGIN
   PRINT '@currPosSource: ' + CONVERT(varchar(20), @currPosSource)
  END
  
  SET @currentCharacter = SUBSTRING( @shortenedID, @currPosSource + 1, 1 )
  IF @debug = 1 
  BEGIN
   PRINT '@currentCharacter: ' + CONVERT(varchar(20), @currentCharacter)
  END
  
  IF @currentCharacter = 'Z'
  BEGIN
   SET @currentCharacter = ' '
  END
  
  SET @currPos = CHARINDEX(@currentCharacter, @ShortChars COLLATE Latin1_General_CS_AS)
  IF @debug = 1 
  BEGIN
   PRINT '@currPos: ' + CONVERT(varchar(20), @currPos)
  END
  
  SET @pow = POWER(@charBase, @i-1)
  IF @debug = 1 
  BEGIN
   PRINT '@pow: ' + CONVERT(varchar(20), @pow)
  END
  
  SET @exploded = @exploded + (@currPos * @pow)
  IF @debug = 1 
  BEGIN
   PRINT '@exploded: ' + CONVERT(varchar(20), @exploded)
  END
  
  SET @i = @i - 1
 END 
 
 SET @idExploded = @exploded
END

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

Job: Mid-level .NET Developer in Saratoga, NY

A friend has "a client in Saratoga that needs a solid mid – to senior level developer – must be .Net C# and SQL. 4-6 years of experience. This person needs to be independent and take ownership of one of the products."

Pay range is decent for upstate NY -- email me a...@b... for details.