Skip to content

Instantly share code, notes, and snippets.

@aevdokimenko aevdokimenko/id2alpha.sql
Last active Jan 15, 2019

Embed
What would you like to do?
T-SQL scalar function to generate pseudonymised character IDs from integer IDs
-- 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
You can’t perform that action at this time.