CODEWITHSUNDEEP
databasevb.netms-access
Marco
Marco

Reputation: 85

find specific part of string while using sql to query a database

I try to query my database retrieving only part of a field name. Currently I use this code:

Dim sSQL = "SELECT Mid([Document Name], Len([Document Name]) - 11, 4) As [Year], Count([Year]) As [Totals] FROM Archief Group By Mid([Document Name], Len([Document Name]) - 11, 4) Order By Mid([Document Name], Len([Document Name]) - 11, 4) DESC ;"
Dim dt As New DataTable
Using cmd As New OleDb.OleDbCommand(sSQL, con3)
    Using reader = cmd.ExecuteReader
        dt.Load(reader)
    End Using
End Using

The field [Document Name] contains office documents constructed like: MYDOCUMENT.NAME.20210317.DOC

The code I use retrieves the year 2021 from the date. The problem occurs when the extension has 4 characters, like: MYDOCUMENT.NAME.20210317.DOCX

I was wondering if SQL can use code to find the last dot (.) in the string and then from there go left 8 characters to find the year.

Upvotes: 0

Views: 364

Answers (1)

Marco
Marco

Reputation: 85

InstrRev solved my problem

Dim sSQL = "SELECT Mid([Document Name], InstrRev([Document Name], '.', Len([Document Name]) - 1) - 8, 4) As [Year], Count([Year]) As [Totals] FROM Archief Group By Mid([Document Name], InstrRev([Document Name], '.', Len([Document Name]) - 1) - 8, 4) Order By Mid([Document Name], InstrRev([Document Name], '.', Len([Document Name]) - 1) - 8, 4) DESC ;"

Upvotes: 1

Related Questions