Skip to content

Instantly share code, notes, and snippets.

@Plutor
Created April 27, 2012 17:33
Show Gist options
  • Save Plutor/2511071 to your computer and use it in GitHub Desktop.
Save Plutor/2511071 to your computer and use it in GitHub Desktop.
BIGINT to base 36 conversion in T-SQL
ALTER FUNCTION dbo.fnBase36
(
@Val BIGINT
)
RETURNS VARCHAR(9)
AS
BEGIN
DECLARE @Result VARCHAR(9) = ''
IF (@Val <= 0)
BEGIN
RETURN '0'
END
WHILE (@Val > 0)
BEGIN
SELECT @Result = CHAR(@Val % 36 + CASE WHEN @Val % 36 < 10 THEN 48 ELSE 55 END) + @Result,
@Val = FLOOR(@Val/36)
END
RETURN @Result
END
GO
@greendimka
Copy link

Line 5: maximum value of BIGINT produces 13 characters when converted to base36. Is VARCHAR(9) used for some special reason? Or can it be replaced with VARCHAR(13)?

@iiaiiappa
Copy link

No special reason. can be replaced with varchar(13).

@marcinjakubowski
Copy link

marcinjakubowski commented Jun 7, 2018

Please do not use this, it doesn't even produce the right results. There is no reason for procedural code and scalar functions are utterly terrible for performance in SQL server. Use this instead:

CREATE FUNCTION dbo.fnBase36x
(
    @val BIGINT
)
RETURNS TABLE
AS
RETURN (
    WITH recursively AS
    (
        SELECT @val AS i
             , CAST(NULL AS VARCHAR(13)) AS result
        
        UNION ALL
        SELECT FLOOR(i/36)
             , CAST(CONCAT(CHAR(i % 36 + CASE WHEN i % 36 < 10 THEN 48 ELSE 55 END), result) AS VARCHAR(13))
          FROM recursively
         WHERE i > 0
    )
    SELECT ISNULL(result, 0) AS result FROM recursively WHERE i = 0
)

Results and usage:

SELECT dbo.fnBase36(1000000000000000) 
-- returns 9UGXNORJL

SELECT * FROM dbo.fnBase36x(1000000000000000)
-- returns 9UGXNORJLS

@Leyline77
Copy link

Thanks @Plutor,
And for everyone wondering, use @Plutor's code NOT* @marcinjakubowski

Note: I updated both functions BIGINT input changed to DECIMAL(38,0)
The output in both functions to varchar(50)

The scalar function has a much faster execution plan.
Also the scalar function is much easier to use for inserts / updates without having to join in / select from the table.

Not to attack @marcinjakubowski, but just some research / proof / facts

The only reason @marcinjakubowski thinks it "does not produce the right results" is because the 1st example returned varchar(9) and the result truncated for the large number provided. IF he used varchar(9) his would also be wrong. Please test on an even playing field.

Creating a common table expression and using recursion, selects, unions, still have to compute scalar variables is faster than a simple loop to compute some math facts...
No way. Hard facts below; look at that execution plan.

Query 1: Query cost (relative to the batch): 10%
Query 2: Query cost (relative to the batch): 90%

image
(If scalar in SQL is evil, the execution plan from @marcinjakubowski as 5 compute scalar hits to the 1 from @Plutor)

--(updated varchar(9) output to varchar(50))  -- They both work
Select dbo.fnBase36(1000000000000000)
--returns 9UGXNORJLS
Select * from dbo.fnBase36xl(1000000000000000)
--returns 9UGXNORJLS

The benefit of DECIMAL(38,0) (more input) and varchar(50) (more output

Select dbo.fnBase36(1001020120340122232223222223222342)
--returns 22VUI4KT01C9TUXJLZWQPY
Select * from dbo.fnBase36x(1001020120340122232223222223222342)
--returns 22VUI4KT01C9TUXJLZWQPY

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment