Adding suffix to a column value for duplicate names in SQL

In this blog, let us see how to add suffix for a field value if the value already exists in the table.

Scenario

Consider a school teaches multiple classes. If the students limits for a particular class(for eg “Marketing Management” )is reached, then new class should be created with same class name but with suffix i.e. (Marketing Management A”.

My tables

I created a table Schoolclass with 4 columns. We need to check with a class name already exists in column “Name “, if so return name with suffix else return name.

CREATE TABLE SchoolClass(
[Class_id] [int] NOT NULL,
[Location] [char](10) NOT NULL,
[Name] [varchar](50) NOT NULL,
MaxStudents[int] NULL,
CONSTRAINT [SchoolClass_PK] PRIMARY KEY CLUSTERED
(
[Class_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = ON, IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

Insert script:

After that I inserted few records using the below insert script.

insert into SchoolClass values
(1, 'Bangalore', 'Marketing Management',25),
(2, 'Chennai', 'Marketing Management',25),
(3, 'Chennai', 'Online Marketing',25),
(4, 'Bangalore', 'Online Marketing',25)

My stored Procedure:

To achieve this, I am using charindex() function in SQL server.

Create  PROC [dbo].[asp_CheckClassNameExists]
@ClassName as varchar(500)
,@DestinationCode as char(10) 
,@NewClassName varchar(500) OUT 
AS   
SET NOCOUNT ON   
BEGIN   
Declare @Count as integer
declare @suffix char(26)

--setting suffix
select @suffix = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
select @Count= COUNT(*) from SchoolClass
where charindex(@classname,name) !=0
and Location=@DestinationCode

--if no records found return classname
if @Count=0
set @NewClassName=@classname

--else return classname with suffix
else
select @NewClassName=@classname + ' '+ SUBSTRING(@suffix,@count,1)
End
END

Verifying the stored proc:

1. declare @NewClassName  varchar(500)
   exec [asp_CheckClassNameExists] 'Online Marketing','Pune',@NewClassName OUT
   select @NewClassName as classname

   Output-Online Marketing
    

2. declare @NewClassName  varchar(500)
   exec [asp_CheckClassNameExists] 'Online Marketing','bangalore',@NewClassName OUT
   select @NewClassName as classname

   Output-Online Marketing A
    

3. declare @NewClassName  varchar(500)
   exec [asp_CheckClassNameExists] 'Marketing Management','bangalore',@NewClassName OUT
   select @NewClassName as classname

   Output- Marketing Marketing A