Sql Server Query varchar sort

Question

0.00/5 (No votes)

See more:

Hello..

i have one table like

CREATE TABLE table_name
(
P_Id int,
amount varchar(50)
)

---------------------------

Data Like

Id amount
--------------------
1 2340
2 4568
3 10000

Now i want to sort table by amount but one problem is amount is varchar so it sort table like this

Id amount
--------------------
3 10000
1 2340
2 4568

but i want like this

Id amount
--------------------
3 10000
2 4568
1 2340

any help please. .

Posted 23-May-13 1:27am

Archit Patel

Add a Solution

3 solutions

Solution 3

Here is the most memorable solution:

USE [cpqaAnswers]
GO
CREATE TABLE [cpqa].[tbl_AP_zeropadSolution003]
(
	P_Id int,
		amount varchar(50)
)
INSERT INTO [cpqa].[tbl_AP_zeropadSolution003]
	VALUES(1, '2340'),
	(2, '4568'),
	(3, '10000')
	
SELECT * FROM [cpqa].[tbl_AP_zeropadSolution003] -- trouble with "ints" as "chars"
SELECT * FROM [cpqa].[tbl_AP_zeropadSolution003] ORDER BY [amount] -- not right (sic)

How many places is the question ... arbitrarily pick a number, here six (6) ... but it doesn't really matter ... the objectionable sort too:

SELECT [P_Id], LEFT(REPLICATE('0', 6), 6 - LEN([amount])) + [amount] AS [zeropadded_amount] FROM [cpqa].[tbl_AP_zeropadSolution003]
/*
 result:

 P_Id	zeropadded_amount
    1	           002340
    2	           004568
    3	           010000
*/

Here's the good sort:

SELECT [P_Id], LEFT(REPLICATE('0', 6), 6 - LEN([amount])) + [amount] AS [zeropadded_amount] FROM [cpqa].[tbl_AP_zeropadSolution003] ORDER BY [zeropadded_amount] DESC
/*
 result:

 P_Id	zeropadded_amount
 ~~~~~~~~~~~~~~~~~~~~~~~~
    3	           010000
    2	           004568
    1	           002340
*/

Posted 23-May-13 9:08am

RedDk

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)

Maciej Los · Accepted Answer · 2013-05-23T01:30:00

Solution 1

Bad table design! Please, re-design table. Numeric values must be numeric not text!

Refer this link: http://msdn.microsoft.com/en-us/library/ms187752.aspx[^]

[EDIT #1]

Please, follow these steps to correct your table design.

SQL

ALTER TABLE table_name
(
    ADD COLUMN namount INT
)

UPDATE t1 SET t1.namount = CONVERT(INT, t2.amount)
FROM table_name AS t1 INNER JOIN table_name AS t2 ON t1.P_Id = t2.P_Id

ALTER TABLE table_name
(
    DROP COLUMN amount
)

SELECT *
FROM table_name
ORDER BY namount

If you don't want to upgrade your table schema, please, use this:

SQL

SELECT *
FROM table_name
ORDER BY CONVERT(INT,amount) DESC

[/EDIT]

Posted 23-May-13 1:30am

Maciej Los

Updated 23-May-13 2:16am

v3

Comments

[no name] 23-May-13 7:32am

i know it bad but now i have this table design and i can't change it i want solution in this if possible than help me.

Maciej Los 23-May-13 7:52am

See my answer now ;)

Arun Vasu 23-May-13 7:54am

don't copy other's solutions

Maciej Los 23-May-13 7:55am

In which part?

Maciej Los 23-May-13 8:00am

Sorry, i see it now.
You post your solution in a mean time, when i have edit my solution ;)
Read my comment to your solution.

Arun Vasu 23-May-13 8:31am

sorry.. mistakenly i said..

Maciej Los 23-May-13 8:50am

OK ;)

Arun Vasu · Accepted Answer · 2013-05-23T01:34:00

Solution 2

SQL

select * from tablename order by convert(int,amount)

please mark answer if its work correctly

Posted 23-May-13 1:34am

Arun Vasu

Comments

Maciej Los 23-May-13 7:56am

Good, but not complete...
+4!

Arun Vasu 23-May-13 8:01am

what not complete? what you mean? i have check the query in my system. and i provide answer.

Maciej Los 23-May-13 8:05am

I see that you do not understand that bad design of table cannot be "corrected" by SELECT statement...

gvprabu 23-May-13 8:13am

Arun,
U posted 36 mins Ago, bur Maciej Los Posted 40 Mins Ago... :-)

Arun Vasu 23-May-13 8:19am

first he posted the answer. then after i put the answer again he update the answer. that updated information is my answer.

gvprabu 23-May-13 8:16am

Default ORDER BY is ASC right then output Should be 1 2340, 2 4568, 3 10000. So we need to add DESC in ORDER BY Clause

gvprabu 23-May-13 8:23am

Why he want to copy your answer?... check his posts. he is well known person and gave many complex solutions in SQL Area Questions in Code Project. Both are posted same time and this question also simple one. Seniors won't copy our solutions.

Arun Vasu 23-May-13 8:33am

sorry mistakenly i said that... i am extremely sorry for that..

Maciej Los 23-May-13 8:50am

Thank you ;)

gvprabu 23-May-13 8:34am

why sorry and all.... Just my Suggestion... pls don't take as serious. He is very nice person.