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?
Posts
3 comments:
--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)
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.
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.
Post a Comment