HOW TO ORDER THE VARCHAR in SQL 2008

Question

3.40/5 (2 votes)

See more:

SQL-Server-2008

How to order the varchar fiels as following output

eg I Have ACCOUNT NO values like

FD2Y-01-10
NS-01-1
DDL-01-3
NS-01-10
NS-01-5
FD2Y-01-1
FD2Y-01-5
NS-01-2
DDL-01-13
DDL-01-1

SELECT ACCOUNTNO FROM MAMACCOUNTOPENING ORDER BY ACCOUNTNO
and the order values should be like

DDL-01-1
DDL-01-3
DDL-01-13
FD2Y-01-1
FD2Y-01-5
FD2Y-01-10
NS-01-1
NS-01-2
NS-01-5
NS-01-10

Posted 23-Jul-14 19:07pm

User 10734264

Updated 23-Jul-14 19:44pm

v2

Add a Solution

Comments

Magic Wonder 24-Jul-14 1:39am

What you have tried?

Magic Wonder 24-Jul-14 2:25am

Is your query not working? What output you are getting?

[no name] 24-Jul-14 2:28am

I am Getting the Output as
DDL-01-1
DDL-01-13
DDL-01-3
FD2Y-01-1
FD2Y-01-10
FD2Y-01-5
NS-01-1
NS-01-10
NS-01-2
NS-01-5

but i need as

DDL-01-1
DDL-01-3
DDL-01-13
FD2Y-01-1
FD2Y-01-5
FD2Y-01-10
NS-01-1
NS-01-2
NS-01-5
NS-01-10

5 solutions

Solution 1

I think you can split the column on the basis of "-" and convert it in to three columns and put it after order by clause

for spliting columns

SQL

select LEFT(name, CHARINDEX(' ', name + '-') -1),
       STUFF(name, 1, Len(Name) +1- CHARINDEX('-',Reverse(name)), '')
from somenames

above query not for your output but this is the example for you .....

Posted 23-Jul-14 19:33pm

Manoj Kumar Choubey

Solution 2

As in your "Question" the VARCHAR values starts with an alphabet, So a normal "ORDER BY Column_name" clause can be used to get the required result.

If you have any problem with the same take a screen shot or write the full Query with the description of database structure.

ALSO: Go to the LINK to Know more about VARCHAR.
http://msdn.microsoft.com/en-us/library/ms176089.aspx[^]

HAPPY CODING....

MY BLOG ADDRESS: http://basetutorial.blogspot.in/[^]

Posted 23-Jul-14 19:36pm

sumitjoshi.mail

Comments

[no name] 24-Jul-14 2:34am

WHEN RUNNING THE SCRIPT
SELECT ACCOUNTNO FROM MAMACCOUNTOPENING ORDER BY ACCOUNTNO

I am Getting the Output as
DDL-01-1
DDL-01-13
DDL-01-3
FD2Y-01-1
FD2Y-01-10
FD2Y-01-5
NS-01-1
NS-01-10
NS-01-2
NS-01-5

but i need as

DDL-01-1
DDL-01-3
DDL-01-13
FD2Y-01-1
FD2Y-01-5
FD2Y-01-10
NS-01-1
NS-01-2
NS-01-5
NS-01-10

sumitjoshi.mail 25-Jul-14 7:02am

Gopal Please refer the MSDN Link that i have mentioned in my answer.

Solution 5

Try this.....

SQL

with a as
(SELECT N'FD2Y-01-10' as col UNION
SELECT 'NS-01-1' UNION
SELECT 'DDL-01-3' UNION
SELECT 'NS-01-10'UNION
SELECT 'NS-01-5' UNION
SELECT 'FD2Y-01-1' UNION
SELECT 'FD2Y-01-5' UNION
SELECT 'NS-01-2' UNION
SELECT 'DDL-01-13' UNION
SELECT 'DDL-01-1'
)
SELECT col FROM a
order BY left(col, charindex('-', col)-1), convert(FLOAT, replace(replace(col,left(col, charindex('-', col)),''),'-','.'))

Happy Coding!
:)

Posted 23-Jul-14 21:09pm

Aarti Meswania

Solution 3

Hi,

Try this..

SQL

SELECT * FROM YOUR_TABLE ORDER BY YOUR_COL

There is nothing special required simple order by clause will work.

Hope this will help you.

Cheers

Posted 23-Jul-14 19:39pm

Magic Wonder

Comments

[no name] 24-Jul-14 2:33am

I am Getting the Output as
DDL-01-1
DDL-01-13
DDL-01-3
FD2Y-01-1
FD2Y-01-10
FD2Y-01-5
NS-01-1
NS-01-10
NS-01-2
NS-01-5

but i need as

DDL-01-1
DDL-01-3
DDL-01-13
FD2Y-01-1
FD2Y-01-5
FD2Y-01-10
NS-01-1
NS-01-2
NS-01-5
NS-01-10

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)

Prakriti Goyal · Accepted Answer · 2014-07-23T20:43:00

Solution 4

I tested this and this is working fine. Here test is my table having one column named name with values you mentioned.

SQL

select name from test
order by substring(name, 1,(len(name)-charindex('-', name))-1), name

Hope this works well with you. :)

Posted 23-Jul-14 20:43pm

Prakriti Goyal