AutoGenrate Number in SQL.

Question

I have table called Call the columns are CallNo  CallNumber  CallDate What i When Call being enter CallNumber should genrate 000001 so on depend upon CallNo. I used in computed Column Specification Formula ('00000'+right(CONVERT([nvarchar](250)[CallNo](0))(5))) If CallNo=1 then i shows 000001 but if Call No=100 then shows 00000100 digit are increase. i do'nt want to increase digit if Call No=100 then i shows 000100 how to do it in SQL. Thanks Basit.

Manas Mohapatra · Answer

Try below code: RIGHT( '00000' + CONVERT(VARCHAR(250) [ CallNo ]) 5)

Khan Abrar Ahmed · Answer

Hi try this below code declare @call varchar(64) =10 --Input parameter BEGIN DECLARE @length INT DECLARE @result VARCHAR(128) SET @result = '' SET @length = 0 DECLARE @appendZero VARCHAR(5) SET @appendZero = '000000' SET @length = Len(ltrim(rtrim(@call))) DECLARE @i INT SET @i = 1 IF (@length > 0) BEGIN WHILE (@i != 5) BEGIN IF (@length = @i) SET @result = Substring(@appendZero 0 (6 - @i)) SET @i = @i + 1 END END SET @result = @result + @call select 'UR-'+@result END

Manas Mohapatra · Answer

Try below code: 'UR-' + RIGHT( '00000' + CONVERT(VARCHAR(250) [ CallNo ]) 5)

basit khan · Answer

Thanks A lot dear.

basit khan · Answer

Thanks Manaus. But if i want to add character then like 'UR- 00000' then what will be the code? I tried RIGHT( 'UR-00000' + CONVERT(VARCHAR(250) [ CallNo ]) 7) but not success. Thanks Basit