CODEWITHSUNDEEP
asp.netsqlregexdatabasems-access
SharpBarb
SharpBarb

Reputation: 1590

Sort Numbers with a Letter Suffix from an Access Database

I am attempting to retrieve some information from an Access database using an OleDbConnection. I am trying to Order the results By a column that contains a set of numbers in string format.

I wanted the results in a natural order (e.g. 1, 2, 10, 20 versus 1, 10, 2, 20), so I converted the data in the column of interest to integers and sorted the results.

"SELECT Drawing, Sheet FROM TableName ORDER BY CINT(Sheet) ASC"

This works fine, except in some cases when the table data has values with a letter suffix (e.g. 1A, 2B, etc...). The command above obviously fails for theses cases.

I would like the results sorted like so: 1, 2, 2A, 2B, 3, 3A, and so on...

So, how to go about this? I've seen some examples that use REGEXP and some conditional statements, but apparently MS SQL doesn't support REGEXP. So I'm stuck. Ideas would be appreciated.

Upvotes: 2

Views: 356

Answers (1)

HansUp
HansUp

Reputation: 97101

There is no way to use a regular expression in a query run from outside an Access session. If the query is run inside an Access session, it could use a custom VBA function with RegExp. But a regular expression approach seems like over-kill for this situation anyway. You can get what you need simply with Val().

The Val Function will return a number from the digits in your string. It will stop reading the string when it hits a letter.

Here's an example from the Immediate window.

? Val("2A")
 2

Use it in your query like this ...

SELECT Drawing, Sheet
FROM TableName
ORDER BY Val(Sheet), Sheet;

Upvotes: 3

Related Questions