sort varchar field in sql server-2005

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

hello sir,

I m working in a real time project. I want a sorting on a code(URC CODE).
Code doesnt have any particular pattern means we can enter anything for this code like numbers,Strings,alphanumeric characters.

I have stored this urccode as a field in database in varchar form.
and using this field in my crystal reports.i have to show urc code in ascending order in my crytsal reports
for ex.

1
2
3
1.2.3
2.1.2
2.1A.2
2.1.1A.3
A1.01
A1.2
A2.1A
A1.2A.2.3

sorted order is:
1
1.2.3

2
2.1.2
2.1A.2
2.1.1A.3

3
A1.1
A1.2
A1.2A.2.3
A2.1A
.
.
.
so on

so can anyone please tell me code for this in sql server-20005
or any blog as i searched many blogs but didnt find any solution for this.

Thanks

Posted 2-Aug-10 0:55am

Varun Sareen

Updated 7-Feb-12 0:51am

v2

Add a Solution

Comments

Sandeep Mewara 2-Aug-10 7:18am

1. Try to be professional. You are not doing an SMS using so much of txtspeak (like plz)
2. It might be urgent for you, but people would reply once they get time. Have patience.

Varun Sareen 7-Feb-12 6:49am

sorry sandeep, I have updated the question

2 solutions

Solution 2

Example:

SELECT [column_name] FROM table_name order by column_name asc

Above is simple query.
If you want you can implement sort by in your stored procedure to get the required output in your crystal report.

Posted 7-Feb-12 1:03am

Sushma_Patel

Updated 7-Feb-12 1:10am

v2

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)

Sandeep Mewara · Accepted Answer · 2010-08-02T01:22:00

Solution 1

Code doesnt have any particular pattern means we can enter anything for this code like numbers,Strings,alphanumeric characters...

Looks like a Dictionary sorting to me. If so, what is the problem? Go ahead and use the SORT method.

There is no *code* for it. You can either sort it in Database while fetching or in your Business Layer before passing the data to Report.

In fact, I think even Sorting of this column in Report should also do for you! Just sort the data by this column in your report.

Posted 2-Aug-10 1:22am

Sandeep Mewara

Comments

Varun Sareen 2-Aug-10 7:46am

Reason for my vote of 2
Dear Sandeep, Actually i couldn't get your answer as this software will be used by multiple user across the country. Every user will enter his own pattern of code to distinguish between the items. So i am confused in sorting the data on a code where there is no regular pattern.? As this is related to reports (Crystal Reports); I have to show the data through a query and that means no front end code can be used and I am not so much proficient in SQL Server programming. If you could now provide any solution to it then kindly let me know.

Thanks

Varun Sareen

Sandeep Mewara 2-Aug-10 8:29am

So? Whatever the pattern is, as long as you need dictionary sorting you can achieve it at any level, SQL or Reports!
BTW, if your question scenario didn't fit my reply you should have added them in your question instead of down-voting! Yet thanks. I don't find why are you unable to sort the field as based on the example provided it's a simple Sorting that can be achieved at DB level or report level (not UI level as you just said you don't have any!)

Varun Sareen 17-Aug-10 3:47am

Dear Sandeep, If you could please elaborate on the solution by providing an example with some code then i will be more clear on it as i haven't used the dictionary sorting yet, I would appreciate your concern.

Thanks

Varun Sareen

Sushma_Patel 7-Feb-12 7:01am

My 5 to Sandeep. Good Answer