BIGINT to base 36 conversion in T-SQL

fnBase36.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

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)?

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

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

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%

(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