How I Can Sort Varchar Values..Pls Help

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

VB

how i can sort below varchar values 
13028-2DEW1
13028-2DEW10
13028-2DEW2
13028-2DEW3
13028-2DFB1
13028-2DLW1
13028-2DLW10
13028-2DLW101
13028-2DLW2
13028-2DLW7

i need output like this

13028-2DEW1
13028-2DEW2
13028-2DEW3
13028-2DEW10
13028-2DFB1
13028-2DLW1
13028-2DLW2
13028-2DLW7
13028-2DLW10
13028-2DLW101

Posted 1-May-14 22:48pm

deenasudhakar

Add a Solution

2 solutions

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)

OriginalGriff · Accepted Answer · 2014-05-01T23:01:00

Basically you can't - the sort order for a string value is always determined on the difference between characters, not a "total numeric value".

But if you convert the "end bit" to a number....

SQL

SELECT * FROM MyTable
ORDER BY SUBSTRING(MyColumn, 1, 10), CAST(SUBSTRING(MyColumn, 11, 1000) AS INT)

But - you might be better off constructing this as a Computed Column[^] and storing the various parts separately - it would save a lot of hassle!

Maciej Los · Accepted Answer · 2014-05-01T23:03:00

Have a look at sample:

SQL

DECLARE @tmp TABLE (code VARCHAR(30))

INSERT INTO @tmp (code)
VALUES('13028-2DEW1'),
('13028-2DEW10'),
('13028-2DEW2'),
('13028-2DEW3'),
('13028-2DFB1'),
('13028-2DLW1'),
('13028-2DLW10'),
('13028-2DLW101'),
('13028-2DLW2'),
('13028-2DLW7')


SELECT code, LEFT(code,10) as begpart, CONVERT(INT, RIGHT(code, LEN(code)-10)) AS endpart
FROM @tmp
ORDER BY LEFT(code,10), CONVERT(INT,RIGHT(code, LEN(code)-10))

Result:

13028-2DEW1	13028-2DEW	1
13028-2DEW2	13028-2DEW	2
13028-2DEW3	13028-2DEW	3
13028-2DEW10	13028-2DEW	10
13028-2DFB1	13028-2DFB	1
13028-2DLW1	13028-2DLW	1
13028-2DLW2	13028-2DLW	2
13028-2DLW7	13028-2DLW	7
13028-2DLW10	13028-2DLW	10
13028-2DLW101	13028-2DLW	101

If you remove begpart and endpart from select list, you'll see only code values.

How I Can Sort Varchar Values..Pls Help

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0