Nov 22, 2006

A Scenario to Ponder #10

Say,I have a requirement. My business demands an auto-generated key (or an identity) for a table called CharTbl. But, it needs to be a character identity column. The sequence generation should be as given in the figure below (partial result displayed).


How would I go about creating the table definition?

3 comments:

  1. AnonymousFriday, November 24, 2006 3:21:00 AM

    --Function that converts the identity value to character
    CREATE FUNCTION dbo.GetCharIdentity(@idval int)
    RETURNS VARCHAR(20)
    AS
    BEGIN

    declare @rem int, @output varchar(10)
    select @output = ''

    while @idval > 0
    begin
    select @rem = CASE WHEN @idval%26 = 0 THEN 26
    ELSE @idval%26 END
    select @idval = CASE WHEN @rem = 26 THEN (@idval-1)/26
    ELSE @idval/26 END
    select @output = char(@rem + 64) + @output
    end

    RETURN @output

    END


    --table definition
    CREATE TABLE [charidval] (
    [idval] [int] IDENTITY (1, 1) NOT NULL ,
    [charidval] AS ([dbo].[GetCharIdentity]([idval])) ,
    [somecolumn] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    CONSTRAINT [PK_charidval] PRIMARY KEY CLUSTERED
    (
    [idval]
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    GO


    --some random values inserted
    insert into charidval
    (somecolumn)
    SELECT ('OMNIBUZZ')
    UNION ALL
    SELECT ('SQL')
    UNION ALL
    SELECT ('GARBAGE')
    UNION ALL
    SELECT ('COLLECTOR')

    --lets save omni some typing
    select * from charidval

    --some more savings
    select 18281, dbo.GetCharIdentity(18281)
    UNION ALL
    select 705, dbo.GetCharIdentity(705)
    UNION ALL
    select 29, dbo.GetCharIdentity(29)

    ReplyDelete
  2. OmnibuzzFriday, November 24, 2006 4:31:00 AM

    Good One RJG (Too long a name you got)! Your solution has, probably, the best performance and maintainability anyone can get.
    And that would be my suggested approach if such a requirement ever comes my way.

    Now to the fun part, throwing maintainability out of the window, can you think how else would you write the function to save omni a bit more from typing.

    ReplyDelete
  3. OmnibuzzWednesday, November 29, 2006 6:42:00 AM

    All the solutions, that I can think of, will require a calculated member calling a function as RJG had suggested. The following are just different flavors of what RJG has given.

    #1 using a while loop.
    CREATE function GetChar(@input int)
    returns varchar(10)
    as
    begin
    declare @output varchar(10)
    while @input > 0
    select @output = char(64 + (@input-1)%26+1) + isnull(@output,''),@input= (@input-1)/26
    return @output
    end

    #2 using a recursive function
    create function getchar(@input int)
    returns varchar(10)
    as
    begin
    return case when @input=0 then '' else dbo.getchar((@input-1)/26) + char(64 + (@input-1)%26+1) end
    end

    Performance wise, while loop is better than the recursive function. Maybe I will write a post on it sometime.

    ReplyDelete