-
-
Save Plutor/2511071 to your computer and use it in GitHub Desktop.
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 |
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
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:
Results and usage: