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?
Add a comment
|
2 Answers
I got my answer :)
/me tips his hat to google, yet again.
Definitions
- 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.
...
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.
Comments
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.
