Algorithm for Auto-Appending a Unique ID to Duplicate Titles

Question

I have a user that enters a piece of data into my database and titles it "Widget Title". If he calls another item "Widget Title", and another, and another, when he sees them in his widget list I'd like to show them like such:

• Some Item
• Widget Title
• Some Other Item
• Widget Title 2
• Widget Title 3
• Yet Another Item
• Widget Title 4

Where each duplicate occurrence has its own appended incrementing id.

Would you do this on the way into the database or when you display the output for the list?

Can I have the algorithm?

Answer 1

Hmm... try something like this before saving:

 SELECT TOP 1 name FROM widgets WHERE name=@newname OR name LIKE (@newname + ' [0-9]' ORDER BY name DESC

That will give you the "last used" name.

• If the result is null, there is no collision
• If the result is the same as @newname, append " 2"
• If the result is anything else, chop @newname and the space off the front, convert to an integer, and increment.

Of course, this will only cover you for 9 duplicates, or a total of 10 instances of a given name. To use this approach for more, you'll need to always use a two-digit suffix (02, 03, etc.) and change the LIKE clause to match.

(You didn't specify an RDBMS, so this is written for Microsoft SQL Server, but IIRC the LIKE wildcard syntax is similar in others.)

Answer 2

I just whipped this up in PHP for use on output and it seems to work well. Would you do anything differently?

    $my_titles_array = array('Some Title',
          'Widget Name',
          'Widget Name',
          'Some Other Title',
          'Widget Name',
          'Yet Another Title',
          'Widget Name'
          );

$counted_values_array = array_count_values($my_titles_array);

foreach ($my_titles_array as $title)
{
    if($counted_values_array[$title] > 1)
    {
     $matches_array = array_keys($my_titles_array, $title);
     $i=1;

     foreach($matches_array as $match)
     {
      if($i != 1)
      {
       $my_titles_array[$match] = $title. ' '. $i;
      }
      $i++;
     }
    }
}

echo highlight_string(print_r($my_titles_array,true),true);

Answer 3

declare @name nvarchar(40)
declare @suffix int
declare @currentName nvarchar(50)
set @name = 'My Document'
set @suffix = 1
set @currentName = @name
while exists (select 1 from [table] where [name] = @currentName)
begin
    set @currentName = @name + ' ' + cast(@suffix as nvarchar(10))
    set @suffix = @suffix + 1
end

@name will end up being "My Document 142" (if there are 141 other copies). Note that if you delete a copy in the middle (say, copy 76), the next copy will "fill in" that hole and be called My Document 76.

This is T-SQL.

Answer 4

I just use a serial PK and show it to the user alongside the text.