sorting of a string column

[Sonia Gupta]

I have the column of type string in the database Following is the data in that column 1 2 11 12 21 abc If i sort the table with the help of this column then the output come in the following manner 1 11 12 2 21 abc can i improve the order actually i want the output like 1 2 11 12 21 abc

Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.

[Laddie]

Why not add order by [column name] ASC

Thanks Laddie Kindly rate if the answer was helpful

[Pete OHanlon]

You could, but only with some extremely fancy logic. It's a string, so you have to abide by the rules of strings. Off the top of my head, I would think that you would have to remove the numeric type strings and sort them independently of the non-numeric strings and then combine the two together.

Deja View - the feeling that you've seen this post before.

My blog | My articles

[Sonia Gupta]

i can use the union query to first select the numeric and then an another union query to sort alphanumeric. Am i doing right?

Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.

[Pete OHanlon]

Sonia Gupta wrote:

i can use the union query to first select the numeric and then an another union query to sort alphanumeric. Am i doing right?

For the love of God Sonia, just try it. I'm not going to type your code out for you.

Deja View - the feeling that you've seen this post before.

My blog | My articles

[Leyu]

Convert the data type of the column on the fly using convert or cast then reorder the converted column using ORDER BY. Make sure the converted type is integer!

[Luc Pattyn]

Hi, I don't think SQL offers the required ordering. The "natural sort order" as used for file names by Windows Explorer is available through P/Invoke, so you can implement an IComparer interface like this:

using System.Runtime.InteropServices; // DllImport

/// <summary>
/// Compares two strings while ignoring case and treating sequences of digits as numbers.
/// (static version).
/// </summary>
public static int Compare(string s1, string s2) {
if (s1!=null && s2!=null) return StrCmpLogicalW(s1, s2);
else return string.Compare(s1, s2, true);
}

[DllImport("shlwapi.dll", CharSet=CharSet.Unicode, ExactSpelling=true)]
private static extern int StrCmpLogicalW(string s1, string s2);

I do not know how you could apply the above to database-based data; I would guess you need to first fill a DataTable, then somehow get that sorted using the IComparer object. :)

Luc Pattyn [Forum Guidelines] [My Articles]

---

This month's tips: - before you ask a question here, search CodeProject, then Google; - the quality and detail of your question reflects on the effectiveness of the help you are likely to get; - use PRE tags to preserve formatting when showing multi-line code snippets.

---

[PIEBALDconsult]

Ordinarily I'd say left pad the numerics with SPACEs then sort, but SQL Server doesn't appear to have a PAD function? :wtf: I thought Oracle did. What Database system are you using? Some ideas for SQL Server:

select * from PadTest order by
REPLICATE(' ',8-LEN(X))+X

or

select * from PadTest order by
case ISNUMERIC(X)
when 1 then REPLICATE(' ',8-LEN(X))+X
else X end

modified on Friday, March 7, 2008 12:57 PM