Hi,
See the below query it might be help you.
Declare @LastDigit NVARCHAR(5)
Select @LastDigit = ISNULL(Substring(CustomerID,13,len(CustomerID)),0) from dbo.Table_9
Set @LastDigit = MAX(Convert(Bigint,ISNULL(@LastDigit,0))) + 1
Set @LastDigit = RIGHT('000' + convert(varchar,@LastDigit),4)
Declare @CustomerID Nvarchar(50)
Set @CustomerID = 'SC-' + substring(convert(varchar,Datepart(yy,GETDATE())),3,4)
+ '-' + convert(varchar,Datepart(mm,GETDATE())) + '-' +
convert(varchar,Datepart(dd,GETDATE()))
+ '-' + @LastDigit
print(@CustomerID)
Thanks,
Viprat