sari larasati sari larasati - 5 months ago 9
SQL Question

Sequence number between string in sql

I have store procedure like this

declare @Tmp varchar(60)
declare @Hasil varchar(60)
declare @PFID varchar(50)
declare @CntPFID int
declare @PrevPFID varchar(60)
DECLARE @CheckLetter CHAR(5)

set @PFID ='PF-FB/ALL/009/MKX/VI/16'

SET @CheckLetter = (UPPER(RIGHT((CAST(SUBSTRING(@PFID,10, 4) AS VARCHAR(5))),3)))

select @CntPFID = count(pfid) from pf where pfid like '%'+ right(@PFID,9) and pfid like ''+ left(@PFID,13) +'%'
if @CntPFID = 1
begin
set @Tmp = (UPPER(RIGHT((CAST(SUBSTRING(@PFID,10, 4) AS VARCHAR(5))),3))) + '-' + '01'
set @Hasil = replace(@PFID, replace(@CheckLetter,' ',''),@Tmp)
end
else
begin
set @Tmp = (UPPER(RIGHT((CAST(SUBSTRING(@PFID,10, 4) AS VARCHAR(5))),3))) + '-0' + cast(@CntPFID as varchar(3))
set @Hasil = replace(@PFID, replace(@CheckLetter,' ',''),@Tmp)
end

select @Hasil


I was stuck when the record data already reach 'PF-FB/ALL/009-09/MKX/VI/16' the next number becoming PF-FB/ALL/009-010/MKX/VI/16 (there is three digits = 010) while I want it will be PF-FB/ALL/009-10/MKX/VI/16 (2 digits running). Is there any dynamically way without using condition else if > 9 ... ?

Answer

The expression you need to change: '-0' + cast(@CntPFID as varchar(3)), try this way:

declare  @CntPFID int;
set @CntPFID = 9;
select '-' + right('00'+ cast(@CntPFID as varchar(3)),2)
set @CntPFID = 11;
select '-' + right('00'+ cast(@CntPFID as varchar(3)),2)