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.
我正在尝试使用OleDbConnection从访问数据库中检索一些信息。我正在尝试使用包含一组字符串格式的数字的列对结果进行排序。
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.
我希望结果按照自然的顺序(例如1、2、10、20和1、10、2、20)，所以我将感兴趣的列中的数据转换为整数并对结果进行排序。
"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.
这种方法很有效，但在某些情况下，表数据具有带有字母后缀的值(例如1A、2B等)。上面的命令显然不能满足这些情况。
I would like the results sorted like so: 1, 2, 2A, 2B, 3, 3A, and so on...
我希望结果排序如下:1、2、2A、2B、3、3A等等……
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.
那么，该怎么做呢?我已经看到了一些使用REGEXP和一些条件语句的示例，但是显然MS SQL不支持REGEXP。我卡住了。的想法,我们将不胜感激。
1 个解决方案
#1
3
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().
无法在从访问会话外部运行的查询中使用正则表达式。如果查询在访问会话中运行，它可以使用带有RegExp的自定义VBA函数。但是，无论如何，正则表达式方法似乎在这种情况下是多余的。只需使用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.
Val函数将从字符串中的数字返回一个数字。当它碰到一个字母时，它会停止读字符串。
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;
#1
3
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().
无法在从访问会话外部运行的查询中使用正则表达式。如果查询在访问会话中运行，它可以使用带有RegExp的自定义VBA函数。但是，无论如何，正则表达式方法似乎在这种情况下是多余的。只需使用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.
Val函数将从字符串中的数字返回一个数字。当它碰到一个字母时，它会停止读字符串。
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;