Last active
January 15, 2019 17:39
-
-
Save aevdokimenko/12c1b728b565880ae1fb58805689644d to your computer and use it in GitHub Desktop.
T-SQL scalar function to generate pseudonymised character IDs from integer IDs
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- The function is used to convert employee ID into a (random) | |
-- character. To achieve randomness a seed should be generated by a caller. | |
-- Note: the number of IDs to be encoded cannot be greater than 62, otherwise | |
-- the function will return conflicting characters. To increase the number of handled | |
-- IDs extended the @chars variable | |
-- Usage: | |
-- select dbo.fn_id2alpha(1223, 42, | |
create function [dbo].[fn_id2alpha](@id int, @seed int, @n int) | |
-- @id - ID to be converted into a random char | |
-- @seed - random number, use select @seed = floor(rand()*100) to init | |
-- @n - the total number of IDs to be randomly encoded, should be no greater than 62 | |
returns nvarchar(1) | |
as | |
begin | |
declare @chars nvarchar(255) | |
select @chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789' | |
if @n > len(@chars) select @n = len(@chars) | |
select @id = (@id + @seed)%@n + 1 | |
return substring(@chars, @id, 1) | |
end |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment