Click here to Skip to main content
15,066,328 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more: , +
hi friends i have one table
like

id  name

1   AAA
2   BBB
3   CCC


and i want to record lik this
id

0001
0002
0003


Thanks to advance
Posted
Comments
[no name] 4-Aug-13 7:46am
   
Okay..... and the problem is what?
ridoy 4-Aug-13 7:48am
   
so what is the problem?

Do you need to convert number ID?
You can do this.

SELECT RIGHT('0000' + CONVERT(varchar, id), 4), name FROM table; 
   
Comments
Srinivas Kalabarigi 4-Aug-13 11:21am
   
Will not work if it exceeds 9, you get an additional 0.
gvprabu 5-Aug-13 7:47am
   
how u r telling this solution is not working after 9..?
Status BreakPoint 5-Aug-13 8:45am
   
Sorry, I can't understand your opinion. When id value is 10, result is '0010'.
gvprabu 5-Aug-13 7:44am
   
its is working...
Srinivas Kalabarigi 5-Aug-13 8:55am
   
Sorry, it is working...
USE [cpqaAnswers]
GO
CREATE TABLE [cpqa].[tbl_RP_zeropadSolution005]
(
	P_Id int,
		amount varchar(50),
			name nvarchar(10)
)

INSERT INTO [cpqa].[tbl_RP_zeropadSolution005]
	VALUES(1, '9', 'Tintin'),
	(2, '361','TakTak'),
	(3, '542','Toucan'),
	(4, '2010','Tallidego')

SELECT * FROM [cpqa].[tbl_RP_zeropadSolution005]

SELECT * FROM [cpqa].[tbl_RP_zeropadSolution005] ORDER BY [amount] 

Compare previous two SELECT to this:
SELECT [P_Id], LEFT(REPLICATE('0', 6), 6 - LEN([amount])) + [amount] AS [zeropadded_amount], [name] FROM [cpqa].[tbl_RP_zeropadSolution005]

SELECT [P_Id], LEFT(REPLICATE('0', 6), 6 - LEN([amount])) + [amount] AS [zeropadded_amount], [name] FROM [cpqa].[tbl_RP_zeropadSolution005] ORDER BY [zeropadded_amount] DESC

Copy and paste the above into New Query and hit F5 to see.
   
Something like this:

SQL
SELECT RIGHT('000'+ CONVERT(VARCHAR, Id), 6) AS Result FROM MyTable;
   
Yo keep the Id filed as it is, since it is auto-increment but while retrieving use the below query:

SQL
SELECT STUFF('0000',4-LEN(Id)+1,LEN(Id),Id) AS Result FROM MyTable;


You need to change the number of 0's and the number (4), if you want a 5 digit number and so on.
   
v2
select
	case
	when(id)=1) then
		'000' + Convert(varchar,id)
	when(len(id)=2) then
		'00' + Convert(varchar,id)
	when(len(id)=3) then
		'0' + Convert(varchar,id)
	
	End
	
	AS print_id
	
 ,* from tablename
   
Comments
gvprabu 5-Aug-13 7:47am
   
we have to use some built-in functions for this not CASE Statements
Manikandan Sekar 5-Aug-13 8:23am
   
Its like hard coding your code, without proper methods and all, you just get the required value using catch it may not support all cases and it may lead to wrong answer when values such as 10000 etc. So use appropriate methods to get your data without using hard code like this.
SQL
select
    case len(id)
    when(1) then
        '000' + Convert(varchar,id)
    when(2) then
        '00' + Convert(varchar,id)
    when(len(3)) then
        '0' + Convert(varchar,id)
    when(len(4)) then
         Convert(varchar,id)
    End

    AS 'IDNO'

 ,* from tableName
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900