CODEWITHSUNDEEP
t-sql
Sleep Paralysis
Sleep Paralysis

Reputation: 469

Adding zeros to varchar

I have this script where I create an ID by taking some characters from the first and last name and adding 1 to it if that combination already exists. I am pretty sure I am close but my issue is this. IF a name Sully Joan is entered I need to get SULJ0002 (2 because someone with that name was already entered, next person will be SulJ0003). My problem is, when getting the nummber back after I have casted it as numeric and added 1 i get back '2', I am trying to devise a way to get back'0002' without hard coding the first three zeros.

DECLARE @stid varchar(12)
DECLARE @amt varchar(12) 
DECLARE @tempsid varchar(12) 
DECLARE @newid varchar(12) 
SELECT @stid=SUBSTRING('Sully',1,3)+SUBSTRING('Joan',1,1)+'0'

 set @amt=(SELECT top 1 studentid FROM studentinfo WHERE studentid LIKE
@stid+'%' order by studentid desc)


 IF COUNT(@AMT)=1
 begin

 Set @tempsid = (select CAST(SUBSTRING(@AMT,6,5)AS NUMERIC(12))+1);
 set @newid=REPLACE(@tempsid,'','0')
 end
 ELSE 
 begin
 set @tempsid='00001';
  end 
         select @tempsid
        SELECT @newid

Upvotes: 0

Views: 60

Answers (2)

Stephan
Stephan

Reputation: 6018

Try this instead:

--Here's your table of StudentID's
DECLARE @yourTable TABLE (StudentID VARCHAR(100));
    --INSERT INTO @yourTable(StudentID)
    --VALUES  ('SULJ0005'),
    --        ('SULJ0006');

--Here's your new name
DECLARE @FirstName VARCHAR(25)= 'Sully',
        @LastName VARCHAR(25)= 'Joan',
        @stID VARCHAR(25),
        @stIdNum INT;

--Grab the four letter part of studentID and set all to Uppercase
SELECT @stid= SUBSTRING(@FirstName,1,3)+SUBSTRING(@LastName,1,1)

--Grab the max studentID with matching four letters and add 1
    --ISNULL will return 0 and then add 1 for the first in its group
SELECT @stIdNum = ISNULL(CAST(RIGHT(MAX(StudentID),4) AS INT),0) + 1 
FROM @yourTable WHERE StudentID LIKE @stid +'%'


SELECT @stid + RIGHT(CONCAT(REPLICATE('0',4),@stIdNum),4) AS studentID

Results:

studentID
---------
SulJ0007

Upvotes: 2

jac
jac

Reputation: 9726

This will work in SQL.

SET @newid = RIGHT(REPLICATE('0', 4) + @TempID, 4)

Upvotes: 4

Related Questions