January 25, 2010 at 1:56 pm
I am trying to generate a unique name for an entity in our system.
The requirements are as follows:
1. The user enters a name for the object.
2. If the name the user entered is unique in the database, use that name.
3. If the name the user entered is not unique, numbers are added to the end of the name until the name is unique.
4. Save the object.
The only way I can think of doing this is using a loop.
Does anyone have a different approach?
DECLARE @Table TABLE
(
ID INT IDENTITY(1,1)
,Name VARCHAR(50)
)
INSERT INTO @Table
VALUES ('blue')
,('green')
,('red')
,('red1')
,('green2')
SELECT *
FROM @Table
DECLARE @UserName VARCHAR(50) = 'green'
DECLARE @NewName VARCHAR(50) = @UserName
DECLARE @i INT = 1
WHILE 1=1
begin
SELECT *
FROM @Table
WHERE Name = @NewName
IF @@ROWCOUNT > 0
begin
SET @NewName = @UserName + CAST(@i AS VARCHAR)
SET @i += 1
end
ELSE
BREAK;
end
SELECT @NewName
January 25, 2010 at 2:04 pm
If you have a Numbers table, you could do this:
;with Names (Name, Number) as
(select @UserName as Name, 0
union all
select @NewName + cast(Number as varchar(10)), Number
from dbo.Numbers
where Number > 0),
Name (Name) as
(select top 1 Names.Name
from Names
left outer join @Table T
on Names.Name = T.Name
where T.ID is null
order by Number)
select @NewName = Name
from Name;
select @NewName;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 25, 2010 at 2:42 pm
Do you think there is a performance benefit to using the Tally table?
January 26, 2010 at 6:35 am
Run each one a million times, or least a few thousand, and see which takes less time. A lot of that will depend on environment it's running in.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 27, 2010 at 7:24 am
Thanks for your help.
I will do the performance testing when I have some free time, and post the results up here.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply