numbers not sorting in order

Question

Im trying to sort a column in order (1 to ...) within sql using asp.net and c#. After doing some research it seems like I need to change my column type in SQL Server to int however this is not possible as the column is storing house numbers and I could end up with a house number of 10a (for example) so currently is set to varchar. Because of this it doesnt correctly sort the column. I have tried casting the relevant column as an int but then when running the application I get an error for its bind in gridview, I've also tried other this as the * 1 but still get errors on the bind part when running

SQL Statement

    SELECT DISTINCT tblcontact.ContactID, tblcontact.Forename, tblcontact.Surname, 
    tbladdress.[House Number], tbladdress.AddressLine1, tbladdress.AddressLine2, 
    tblcontact.[Business Name] FROM tblcontact INNER JOIN tbladdress ON tblcontact.AddressID = tbladdress.AddressID 
    LEFT OUTER JOIN tblDonate 
    ON tblcontact.ContactID = tblDonate.ContactID 
    WHERE (tbladdress.CollectionArea = @CollectionArea) AND 
(tbladdress.AddressLine1 = @drpCollectionStreet) 
    ORDER BY tbladdress.[House Number] ASC

Gridview Markup

                                    <asp:TemplateField HeaderText="House Number">
                                        <EditItemTemplate>
                                            <asp:TextBox ID="txtHouseNum" runat="server" Text='<%# Bind("[House Number]") %>'></asp:TextBox>
                                        </EditItemTemplate>
                                        <ItemTemplate>
                                            <asp:Label ID="lblHouseNum" runat="server" Text='<%# Bind("[House Number]") %>'></asp:Label>
                                        </ItemTemplate>
                                    </asp:TemplateField>

Here's an example of how its sorting

This is how I cast as int which sorts fine within SQL but I get an error on the Bind (above)

SELECT DISTINCT tblcontact.ContactID, tblcontact.Forename, tblcontact.Surname, cast(tbladdress.[House Number] as int), 
tbladdress.AddressLine1, tbladdress.AddressLine2, tblcontact.[Business Name] 
FROM tblcontact INNER JOIN tbladdress ON tblcontact.AddressID = tbladdress.AddressID 
LEFT OUTER JOIN tblDonate ON tblcontact.ContactID = tblDonate.ContactID 
WHERE (tbladdress.CollectionArea = 'Queens Park') AND (tbladdress.AddressLine1 = 'Kings Road') 
ORDER BY cast(tbladdress.[House Number] as int)

Error in runtime

this is the error when running (this is the most meaningful)

 "House Number is neither a DataColumn nor a DataRelation for table DefaultView."}

When you say 'it doesn't correctly sort the column', how do you expect it to be sorted, and how is it actually sorted? Can you give a small, 3-5 row sample of 'actual' and 'expected' behavior? — Rufus L, Jan 01 '15 at 20:41
answers to quesions above I want it sorted numerically ascending — AShah, Jan 01 '15 at 20:51
this might help: http://stackoverflow.com/questions/18625548/t-sql-select-query-to-remove-non-numeric-characters — DoubleJ, Jan 01 '15 at 21:49
If the field is a string then it will sort as a string. You need to bind to an integer field to get the sort order you are looking for. What errors do you get when you try it? — Corey, Jan 01 '15 at 21:51
this is the error when running (this is the most meaningful) "House Number is neither a DataColumn nor a DataRelation for table DefaultView."} — AShah, Jan 01 '15 at 22:36

score 1 · Answer 1 · answered Jan 01 '15 at 21:54

To sort the street number numerically you need to cast them as numbers. The trouble, of course, is that strings such as "10a" cannot be cast as an INT. The answer is to extract the numeric characters from your [House Number] column and then do the cast. The following does this for the ORDER BY clause:

... 
ORDER BY CAST(SUBSTRING(tbladdress.[House Number], PATINDEX('%[0-9]%', tbladdress.[House Number]),
                 1 + PATINDEX('%[0-9][^0-9]%', tbladdress.[House Number] + ' ') -
                 PATINDEX('%[0-9]%',tbladdress.[House Number])) AS INT)

See http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/extracting-numbers-with-sql-server/ for an explanation of how the complicated looking formula works.

casting at the order gives an error - ORDER BY items must appear in the select list if SELECT DISTINCT is specified. Then when I cast as int the column [House Number] as above then I get an error in Runtime here -> '<%# Bind("[House Number]") %> (error is above) — AShah, Jan 02 '15 at 11:34

Pரதீப் · Answer 2 · 2015-01-02T11:28:12.857

Remove the cast from select column list. Keep only in order by still it will order the result.

select A.ContactID,
                A.Forename,
                A.Surname,
                A.[House Number],
                A.AddressLine1,
                A.AddressLine2,
                A.[Business Name] from (
SELECT DISTINCT tblcontact.ContactID,
                tblcontact.Forename,
                tblcontact.Surname,
                tbladdress.[House Number],
                tbladdress.AddressLine1,
                tbladdress.AddressLine2,
                tblcontact.[Business Name]
FROM   tblcontact
       INNER JOIN tbladdress
               ON tblcontact.AddressID = tbladdress.AddressID
       LEFT OUTER JOIN tblDonate
                    ON tblcontact.ContactID = tblDonate.ContactID
WHERE  ( tbladdress.CollectionArea = 'Queens Park' )
       AND ( tbladdress.AddressLine1 = 'Kings Road' )) A
ORDER  BY Cast(tbladdress.[House Number] AS INT)

this doesnt work gives the below error (hence the reason for putting the cast in the column) - ORDER BY items must appear in the select list if SELECT DISTINCT is specified. — AShah, Jan 02 '15 at 11:25
this looks good slight amendment the Cast(tbladdress.[House Number] AS INT) should be Cast(A.[House Number] AS INT) - Also if the house number is 11b this wont work as it fails to cast as an integer I think I need a combination of this answer and the bottom one by JohnS — AShah, Jan 02 '15 at 11:48

score 0 · Accepted Answer · answered Jan 02 '15 at 11:53

Correct Answer is a combination of JohnS and NoDisplayName answers thanks to you both

select A.ContactID,
                A.Forename,
                A.Surname,
                A.[House Number],
                A.AddressLine1,
                A.AddressLine2,
                A.[Business Name] from (
SELECT DISTINCT tblcontact.ContactID,
                tblcontact.Forename,
                tblcontact.Surname,
                tbladdress.[House Number],
                tbladdress.AddressLine1,
                tbladdress.AddressLine2,
                tblcontact.[Business Name]
FROM   tblcontact
       INNER JOIN tbladdress
               ON tblcontact.AddressID = tbladdress.AddressID
       LEFT OUTER JOIN tblDonate
                    ON tblcontact.ContactID = tblDonate.ContactID
WHERE  ( tbladdress.CollectionArea = 'Queens Park' )
       AND ( tbladdress.AddressLine1 = 'Kings Road' )) A
       ORDER BY CAST(SUBSTRING(A.[House Number], PATINDEX('%[0-9]%', A.[House Number]),
                 1 + PATINDEX('%[0-9][^0-9]%', A.[House Number] + ' ') -
                 PATINDEX('%[0-9]%',A.[House Number])) AS INT)

numbers not sorting in order

3 Answers3