Sunday, February 21

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

blog comments powered by Disqus