Convert Varchar2 in number Sorting

[Denden17]

Hello . need help about sql statements im using this in my VB.net project
about sorting Varchar2 datatype like a Number for example

//I want this to be outputed.
1
2
3
4
4A
4B
5
6

But when i use this

SELECT PUROK FROM TBLPUROK ORDER BY ldap(PUROK,10) ASC

1
2
3
4
5
6
4A
4B

Heres what it goes.

Hoping for answeres. I already search it and googled i havent got the answer yet. and some are not clearly stated. :(

[lolafuertes]

Please, can you clarify what is supposed to return the ldap(PUROK,10)?

maybe the sentence SELECT PUROK FROM TBLPUROK ORDER BY PUROK ASC does the trick.

Hope this helps

[Denden17]

supposedly i have a ORACLE database and its Datatype is Varchar2
Sample DB

Purok
1
10
11
2
23
3
4
5A
6A

thats the original value. however i want to sort it, i know it would be in the SQL statement but i cant get it.

so its output would be.
1
2
3
4
5A
6A
10
11
23

thats the output i want to get .:(

Thnx for the reply but i already done that and it wont display the result that ive wanted.

[Reverend Jim]

I think you'll have to sort the data from within a VB data structure unless you write a SQL stored procedure to do the query. Any sorting of the records as strings (varchar) will put records (for example) starting with "23" after "2" and before "3" which is not what you want. You'd have to convert each field to numeric after stripping of the non-numeric trailing characters then do a sort on the resulting data.

[Denden17]

yea. thats my idea. but however i cant find the right code for it.

[Reverend Jim]

Try this

    Dim con As New ADODB.Connection
    Dim rec As New ADODB.Recordset
    Dim srt As New SortedDictionary(Of Integer, String)

    con.Open("Driver={SQL Server};Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")
    rec.Open("SELECT myfield FROM mytable", con, CursorTypeEnum.adOpenStatic)

    Do Until rec.EOF
        Dim str As String = rec(0).Value
        Dim num As String = System.Text.RegularExpressions.Regex.Match(str, "[0-9]*").Value
        srt.Add(CInt(num), str)
        rec.MoveNext()
    Loop

    rec.Close()
    con.Close()

    For Each num As Integer In srt.Keys
        TextBox1.AppendText(srt(num) & vbCrLf)
    Next

Inside the loop we first get the field value, then we pull out the first part of the string containing the number (note that because we are using a dictionary no duplicate numbers are allowed. Let me know if you need a version that handles duplicates). Then we convert the numeric string to a number and add the field value to the dictionary using the number as the key. Because we are using a sorted dictionary, when we step through the keys, the values will come out in sorted (according to the number) order.

[Reverend Jim]

The following will handle the case where the records contain entries like 16C, 16D as long as the integers are no larger than 10 digits

        Dim con As New ADODB.Connection
        Dim rec As New ADODB.Recordset
        Dim srt As New SortedDictionary(Of String, String)

        con.Open("Driver={SQL Server};Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")
        rec.Open("SELECT UserName FROM Users", con, CursorTypeEnum.adOpenStatic)

        Do Until rec.EOF
            Dim str As String = rec(0).Value
            Dim num As String = Regex.Match(str, "[0-9]*").Value
            str = str.Substring(Len(num))
            num = CInt(num).ToString("0000000000")
            srt.Add(num & str, rec(0).Value)
            rec.MoveNext()
        Loop

        rec.Close()
        con.Close()

        For Each num As String In srt.Keys
            TextBox1.AppendText(srt(num) & vbCrLf)
        Next