How to sort a nvarchar in sql

Question

0.00/5 (No votes)

See more:

i have mixed data as permit no i need to sort that field

eg:
permit_no

VB

01/13-14/ID/2HY
02/13-14/ID/2HY
03/13-14/ID/2HY
04/13-14/ID/2HY
05/13-14/ID/2HY

like this
when i'am trying to sort the result is comming 100 after 10
eg:

VB

08/13-14/ID/2HY
09/13-14/ID/2HY
10/13-14/ID/2HY
100/13-14/ID/2HY
101/13-14/ID/2HY
102/13-14/ID/2HY

Posted 20-Oct-13 21:32pm

itsmehaboob

Add a Solution

Comments

Sampath Kumar Sathiya 21-Oct-13 3:36am

The sorting is correct only because it is string.

Thanks7872 21-Oct-13 3:40am

Then you have sorted,whats the problem?

2 solutions

Solution 2

visit link
http://blog.sqlauthority.com/2008/10/14/sql-server-get-numeric-value-from-alpha-numeric-string-udf-for-get-numeric-numbers-only/[^]

SQL

SELECT 
   permit_no 
from
   table_name
Order by
   dbo.udf_GetNumeric('permit_no');

Happy Coding!
:)

Posted 20-Oct-13 22:38pm

Aarti Meswania

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 · 2013-10-20T22:04:00

String sorting will always give this result, because it works on a character-by-character basis rather than looking at the data as a set of numeric fields.
If you want to sort it by numeric values, you will have to use SUBSTRING[^] to extract the numeric portions, and then CAST or CONVERT it to a numeric value. This is going to complicate your SORT BY statement horribly, so I would suggest that you produce an SQL function to extract the relevant field number and return it as an integer for sorting - you will need to SORT BY extract(myColumn, 1), extract(myColumn, 2), etc to cope with your multiple numeric values, as well as the text based code at the end.

Good luck! (I think you are going to need it)