I'm look for any help to concatenate an integer.
Example:
In a company a employee as a employee number. And is number is 140024.
Now, the number 14 is the year (depending on the date), and it needs to be assigned automatically. The other number 0024 is my problem. I could get the number 24 but how can I add the 00 or 000 if the number is less than 10?
So I need help to concatenate all this. And also wanted to get it as an INT to make it as a primary key.
DECLARE @Your_Number INT = 24;
SELECT CAST(RIGHT(YEAR(GETDATE()), 2) AS NVARCHAR(2))
+ RIGHT('000000000' + CAST(@Your_Number AS NVARCHAR), 4) --<-- This 4
RESULT: 140024
The Number 4 Decides how many Total digits you want after the Year Digits.
you have two choice : working with varchar or int itself.
Example :
select cast(14 as char(2)) + right('0000' + cast(24 as varchar(4)),4)
or with int
select 14 * 10000 + 24
Where 10000 of course is the number you can have max. It could be 100, 1000 or more. But your number of digit is probably fixed so it should be fixed too.
try this..
CREATE FUNCTION [dbo].[fn_GetAutoGeneratedID]
(
@strPart VARCHAR(20),@strSeprator VARCHAR(5),@intPart VARCHAR(10)
)RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @prefix VARCHAR(10)
SET @prefix=(SELECT CASE LEN(@intPart)
WHEN 1 THEN @strSeprator+'0000'+@intPart
WHEN 2 THEN @strSeprator+'000'+@intPart
WHEN 3 THEN @strSeprator+'00'+@intPart
WHEN 4 THEN @strSeprator+'0'+@intPart
ELSE @strSeprator+@intPart
END)
RETURN(SELECT @strPart+@prefix);
END
now call it as..
SELECT @MyNum=dbo.fn_GetAutoGeneratedID (@yourMonthPart,'',@YourNextpart )
Declare @i int=24
select replicate('0',4-len(cast(@i as varchar(10))))+cast(@i as
varchar(10))
