problem to fetch record to table

Question

1.00/5 (1 vote)

See more:

SQL-Server-2005

, +

hi friends i have one table
like

id  name

1   AAA
2   BBB
3   CCC

and i want to record lik this

Thanks to advance

Posted 4-Aug-13 1:45am

Rashmikants Monpara

Add a Solution

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?

6 solutions

Solution 5

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.

Posted 5-Aug-13 9:03am

RedDk

Solution 2

Something like this:

SQL

SELECT RIGHT('000'+ CONVERT(VARCHAR, Id), 6) AS Result FROM MyTable;

Posted 4-Aug-13 4:46am

Manas Bhardwaj

Solution 3

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.

Posted 4-Aug-13 5:20am

Srinivas Kalabarigi

Updated 4-Aug-13 5:41am

v2

Solution 6

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

Posted 6-Aug-13 23:09pm

qqlww1987

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Status BreakPoint · Accepted Answer · 2013-08-04T04:19:00

Solution 1

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

SELECT RIGHT('0000' + CONVERT(varchar, id), 4), name FROM table;

Posted 4-Aug-13 4:19am

Status BreakPoint

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...

Rashmikants Monpara · Accepted Answer · 2013-08-05T01:38:00

Solution 4

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

Posted 5-Aug-13 1:38am

Rashmikants Monpara

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.