SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How to generate random id and places zero infront
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shinelawrence
Starting Member

India
32 Posts

Posted - 04/28/2014 :  13:58:50  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 04/28/2014 :  16:24:33  Show Profile  Reply with Quote
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);
Go to Top of Page

shinelawrence
Starting Member

India
32 Posts

Posted - 04/29/2014 :  00:46:13  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 04/29/2014 :  03:02:27  Show Profile  Reply with Quote
CAST(@max3DigitsNumber + 1AS VARCHAR(3)),3);



KH
Time is always against us

Go to Top of Page

shinelawrence
Starting Member

India
32 Posts

Posted - 04/29/2014 :  03:42:43  Show Profile  Reply with Quote
thank you very much... It's working perfectly....

Lawce
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000