Anyone have a URL Shortening T-Sql code?

Question

Hi folks,

I'm wanting to shorten some url's and sql guids into some 'short' url format.

Does anyone have any code or links to some code, for url shortening in T-Sql?

Answer 1

Since string manipulation is something T-SQL doesn't do so well, this is something I'd use a CLR stored procedure for. Then you can use any .Net shortening function.

Answer 2

I got my answer :)

/me tips his hat to google, yet again.

Definitions

1. Base36 == a-z 0-9

this means, i want a shortened url. so i insert it into a db to grab a unique identity number. I then convert this int/big int to a base36 string.

Links I based my code off.

1. Converting ints to base36.
2. Converting base36 to ints.

...

CREATE FUNCTION [dbo].[ConvertIntegerToBase36] 
(
    @IntegerValue INTEGER
)
RETURNS VARCHAR(50)
AS
BEGIN

    DECLARE @Result VARCHAR(100) = '',
        @ShortCharacterSet VARCHAR(36) = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'

    WHILE @IntegerValue > 0 BEGIN
        SET @Result = SUBSTRING(@ShortCharacterSet, @IntegerValue % LEN(@ShortCharacterSet) + 1, 1) + @Result;
        SET @IntegerValue = @IntegerValue / LEN(@ShortCharacterSet);
    END

    RETURN @Result
END

...

CREATE FUNCTION [dbo].[ConvertBase36ToInteger]
(
    @EncodedValue VARCHAR(MAX)
)
RETURNS INT
AS
BEGIN
    -- Decoding encoded-strings to ints: http://dpatrickcaldwell.blogspot.com/2009/05/converting-hexadecimal-or-binary-to.html

    DECLARE @Result INTEGER = 0,
        @Index INTEGER = 0,
        @ShortCharacterSet VARCHAR(36) = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'

    WHILE @Index < LEN(@EncodedValue)  
         SELECT @Result = @Result + POWER(LEN(@ShortCharacterSet), @Index) *   
                          (CHARINDEX  
                             (SUBSTRING(@EncodedValue, LEN(@EncodedValue) - @Index, 1)  
                             , @ShortCharacterSet) - 1  
                          ),  
                @Index = @Index + 1;  


    RETURN @Result
END 

HTH.