Search code examples
c#sqldynamic-sqluniqueidentifieridentifier

How do I generate the shortest database identifier names from a number?


Because I am writing software which generates SQL with a potentially large number of parameters and records that SQL onto disk, I have an uncommon requirement (perhaps more a curiosity): Generate the shortest possible unique parameter names.

Parameter names follow identifier naming rules which are usually:

  1. First character is alphabetic
  2. Subsequent characters can be alphanumeric or certain other characters, such as an underscore.
  3. Almost anything can be used if quoted (ignored -- a quoted identifier is at least three characters total, e.g. [_])

The SQL generation code knows how many identifiers there are total, so names can be generated based on an integer.


Solution

  • Code above produces collisions. Fixed code without collisions and magic numbers.

        public static String intToDatabaseIdentifier(int number)
        {
            const string abcFirst = "abcdefghijklmnopqrstuvwxyz";
            const string abcFull = "abcdefghijklmnopqrstuvwxyz0123456789";
            if (number < 0 || number > 1000000)
                throw new ArgumentOutOfRangeException("number");
            var stack = new Stack<char>();
            //Get first symbol. We will later reverse string. So last - will be first. 
            stack.Push(abcFirst[number % abcFirst.Length]);
            number = number / abcFirst.Length;
            //Collect remaining part
            while (number > 0)
            {
                int index = (number - 1) % abcFull.Length;
                stack.Push(abcFull[index]);
                number = (number - index) / abcFull.Length;
            }
            //Reversing to guarantee first non numeric.
            return new String(stack.Reverse().ToArray());
        }