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