Comments on SQL Garbage Collector: A Scenario to Ponder #10

All the solutions, that I can think of, will requi...

2006-11-29T06:42:00.000-05:00

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.

Good One RJG (Too long a name you got)! Your solu...

2006-11-24T04:31:00.000-05:00

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.

--Function that converts the identity value to cha...

2006-11-24T03:21:00.000-05:00

--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)