Order by Alphanumeric values in SQL query using C# and VB.Net in ASP.Net

Hi Lingers, 

Please refer below sample.

Note: Create stored procedure of the select query and call in your code.

HTML

<table>
    <tr>
        <th>Name:</th>
        <td><asp:TextBox runat="server" ID="txtName" CssClass="autosuggest3" /></td>
        <td><asp:Button Text="Search" runat="server" ID="btnSearch" /></td>
    </tr>
</table>
<asp:HiddenField ID="hfjobid" runat="server" Value="15" />
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.24/themes/smoothness/jquery-ui.css" />
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.24/jquery-ui.min.js"></script>
<script>
    $(function () {
        SearchText3();
    });
    function SearchText3() {
        $(".autosuggest3").autocomplete({
            source: function (request, response) {
                $.ajax({
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    url: "Default.aspx/GetAutoCompleteData3",
                    data: "{ 'searchTerm': '" + request.term + "', id: '" + $('[id*=hfjobid]').val() + "'}",
                    dataType: "json",
                    success: function (data) {
                        if (data.d.length > 0) {
                            response($.map(data.d, function (item) {
                                return {
                                    label: item.split('/')[0],
                                    val: item.split('/')[1]
                                }
                            }));
                        }
                        else {
                            response([{ label: 'No Records Found', val: -1 }]);
                        }
                    },
                    error: function (result) {
                        alert(result.responseText);
                    }
                });
            },
            select: function (event, ui) {
                if (ui.item.val == -1) {
                    return false;
                }
                $('[id*=hfcylinderid]').val(ui.item.val);
                $(event.target).autocomplete("close");
                setTimeout(function () {
                    $(event.target).blur();
                });
            },
            minLength: 0
        }).focus(function () {
            $(this).autocomplete("search");
        });
    }
</script>

Namespaces

C#

using System.Data.SqlClient;
using System.Web.Services;

VB.Net

Imports System.Data.SqlClient
Imports System.Web.Services

Code

C#

[WebMethod]
public static List<string> GetAutoCompleteData3(string searchTerm, string id)
{
    List<string> result = new List<string>();
    using (SqlConnection con = new SqlConnection("Data Source=192.168.0.100\\SQL2019;UID=sa;PWD=pass@123;Initial Catalog=test;"))        
    {
        using (SqlCommand cmd = new SqlCommand("SELECT id, pid, posino FROM job_cylinder where posino LIKE @SearchText+'%' AND pid=@Id ORDER BY CAST(LEFT(posino, CHARINDEX('-', posino)-1)  AS INT), CAST(LEFT(RIGHT(posino, LEN(posino)-CHARINDEX('-', posino)), PATINDEX('%[^0-9]%', RIGHT(posino, LEN(posino)-CHARINDEX('-', posino))+'.') - 1)  AS INT), RIGHT(posino, LEN(posino)-CHARINDEX('-', posino))", con))
        {
            con.Open();
            cmd.Parameters.AddWithValue("@SearchText", searchTerm);
            cmd.Parameters.AddWithValue("@Id", id);
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                result.Add(string.Format("{0}/{1}", dr["posino"], dr["id"]));
            }
            return result;
        }
    }
}

VB.Net

<WebMethod>
Public Shared Function GetAutoCompleteData3(ByVal searchTerm As String, ByVal id As String) As List(Of String)
    Dim result As List(Of String) = New List(Of String)()
    Using con As SqlConnection = New SqlConnection("Data Source=192.168.0.100\SQL2019;UID=sa;PWD=pass@123;Initial Catalog=test;")
        Using cmd As SqlCommand = New SqlCommand("SELECT id, pid, posino FROM job_cylinder where posino LIKE @SearchText+'%' AND pid=@Id ORDER BY CAST(LEFT(posino, CHARINDEX('-', posino)-1)  AS INT), CAST(LEFT(RIGHT(posino, LEN(posino)-CHARINDEX('-', posino)), PATINDEX('%[^0-9]%', RIGHT(posino, LEN(posino)-CHARINDEX('-', posino)) + '.') - 1)  AS INT), RIGHT(posino, LEN(posino)-CHARINDEX('-', posino))", con)
            con.Open()
            cmd.Parameters.AddWithValue("@SearchText", searchTerm)
            cmd.Parameters.AddWithValue("@Id", id)
            Dim dr As SqlDataReader = cmd.ExecuteReader()
            While dr.Read()
                result.Add(String.Format("{0}/{1}", dr("posino"), dr("id")))
            End While
            Return result
        End Using
    End Using
End Function

Screenshot