Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shinelawrence
Starting Member
32 Posts |
 Posted - 2014-04-28 : 13:58:50
|
| Hi Everyone, I generated one random ID in sql based one Employee name, i generated but the zero doesn't come. EG:Employee Id : L001 it is the first id of starting letter 'L', again i created another id for starting letter 'L' it shows L2 only instead of L002. If I hotcode 00 is previously the after 99th record it shows L0100 instead of 'L100'. How to do. Please tell the solution.This is my example Query:DECLARE @fname varchar(20)set @fname='L099'select CONVERT(varchar(10),(SUBSTRING('L056',2,LEN(@fname))+1))SELECT SUBSTRING(@fname,1,1)+'0'+CONVERT(varchar(10),(SUBSTRING(@fname,2,LEN(@fname))+1)) FROM Aud_UserRegister WHERE FName ='Lawrence'Lawce |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-04-28 : 16:24:33
|
Not sure the logic you have implemented, but something like this should work for you:DECLARE @max3DigitsNumber INT = 2; SELECT 'L' + RIGHT('000' + CAST(@max3DigitsNumber AS VARCHAR(3)),3); |
 |
|
|
shinelawrence
Starting Member
32 Posts |
Posted - 2014-04-29 : 00:46:13
|
| thanks for ur reply...but if i add plus one it should be change like this...Query:DECLARE @max3DigitsNumber INT = 2; SELECT 'L' + RIGHT('000' + CAST(@max3DigitsNumber AS VARCHAR(3))+1,3);O/P: L3.. but i need L003... How to do...Please tell the solution...Lawce |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2014-04-29 : 03:02:27
|
[code]CAST(@max3DigitsNumber + 1AS VARCHAR(3)),3);[/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
shinelawrence
Starting Member
32 Posts |
Posted - 2014-04-29 : 03:42:43
|
| thank you very much... It's working perfectly....Lawce |
|
|
|
|
|
|
|
|
How to generate random id and places zero infront