Home / Questions / 245265
Accepted
Martin Fernandez
Asked: 2020-03-14 02:11:28 +0800 CST 2020-03-14 02:11:28 +0800 CST

Sort code table with string format (-xx)

  • 772

I am making an application that is handled with codes, many times this data must be consulted ordered by the serial number Example:

Codigo-1
Codigo-2
Codigo-3
Codigo-4

So far so good, but when I handle a lot of data this happens

Codigo-1
Codigo-10
Codigo-11
Codigo-12
Codigo-2
Codigo-3
Codigo-4
Codigo-5
Codigo-6
Codigo-7
Codigo-8
Codigo-9

For what is this?

If it is useful, the way I use to see this data is through a datagridview that is fed by a SQL Server database.

Dim conndgt As New SqlConnection
    conndgt.ConnectionString = conexion
    rutadgt = New SqlCommand("Select * FROM OrdenesDeCompraExion WHERE NumeroDeOrden='" + ndo.Text + "'", conndgt)
    Dim adapterdgt As New SqlDataAdapter(rutadgt)
    Dim tabledgt As New DataTable()
    conndgt.Open()
    Try
        adapterdgt.Fill(tabledgt)

        If tabledgt.Rows.Count() > 0 Then
            DataGridView1.DataSource = tabledgt
        End If

    Catch ex As Exception
        DataGridView1.DataSource = Nothing
        MsgBox("Revise el numero de orden de compra." + vbNewLine + ex.Message)

    End Try

I remember that in Excel the problem is the same, they are grouped in the same way.

vb.net

1 Answers

  1. Best Answer

    The point is that being a string, the order is made as such. And, taken as a string, "10"<"2".

    There are many solutions to the problem, depending on each specific case. One that you can use is the following, making use of Linq.

    Assuming that the pattern is always texto-numero, what you should do is sort only by the numerical part, after converting to Int.

    I'll give you an example using a List. If it is another type of structure, the form would be very similar:

    Dim codigos As List(Of String) = New List(Of String) From {
    "Codigo-1",
    "Codigo-2",
    "Codigo-3",
    "Codigo-10",
    "Codigo-11",
    "Codigo-12",
    "Codigo-4",
    "Codigo-5",
    "Codigo-6",
    "Codigo-7",
    "Codigo-8",
    "Codigo-9"
}
Dim ordenado = codigos.OrderBy(Function(x) Integer.Parse(x.Split("-"c)(1)))

    The "magic" is in the last function. As you can see, the first thing that is applied to each element is Split("-"c), which divides it into Codigoon the one hand, and the number on the other. The number is in position 1 of the array returned by Split, and what we do is apply it Integer.Parseto convert it into an integer. This is the result you use OrderByto return the collection correctly sorted using only the numeric part.

    Since you are using a DataTable, you must use AsEnumerablein order to apply linq to it. It would be something like this:

    If tabledgt.Rows.Count() > 0 Then
    Dim ordenado = tabledgt.AsEnumerable().OrderBy(Function(x) Integer.Parse(x.Split("-"c)(1)))
    DataGridView1.DataSource = ordenado
End If
    • 1