如何编写区分大小写的MS Access查询? [英] How to write Case Sensitive Query for MS Access?
问题描述
我想知道区分大小写的MS Access选择查询.
I want to know the Select Query for MS Access with case sensitive.
我有如下两个VitualMonitorName值
I have two values for VitualMonitorName as below
VCode VirtualMonitorName
Row 1 (1, 'VM1');
Row 2 (2, 'Vm1');
这两个值都不同.
如果我写
"SELECT VCode FROM VirtualMaster WHERE VirtualMonitorName like '" + Vm1 + "'";
它仅回复VCode = 1.
It replies VCode = 1 Only.
推荐答案
您可以将StrComp()
函数与vbBinaryCompare
一起使用,以进行区分大小写的比较.这是即时"窗口中的示例,用于显示StrComp()
的工作方式.有关更多详细信息,请参见Access帮助主题.
You can use the StrComp()
function with vbBinaryCompare
for a case-sensitive comparison. Here is an example from the Immediate window to show how StrComp()
works. See the Access help topic for more details.
? StrComp("a", "A", vbBinaryCompare)
1
? StrComp("a", "A",vbTextCompare)
0
如果前两个参数的值相等,则
StrComp()
返回0;如果两个参数不相等,则返回1或-1;如果其中一个参数为Null,则返回Null.
StrComp()
returns 0 if the first two arguments evaluate as equal, 1 or -1 if they are unequal, and Null if either argument is Null.
要在查询中使用该函数,请提供vbBinaryCompare
常量的值(0)而不是其名称.
To use the function in a query, supply the vbBinaryCompare
constant's value (0) rather than its name.
SELECT VCode
FROM VirtualMaster
WHERE StrComp(VirtualMonitorName, "Vm1", 0) = 0;
如果其他应用程序使用更新的Access Database Engine("ACE")驱动程序,则这些方法也可用于它们.例如,以下C#代码
This approach is also available to queries from other applications if they use the newer Access Database Engine ("ACE") drivers. For example, the following C# code
string myConnectionString =
@"Driver={Microsoft Access Driver (*.mdb, *.accdb)};" +
@"Dbq=C:\Users\Public\Database1.accdb;";
using (OdbcConnection con = new OdbcConnection(myConnectionString))
{
con.Open();
using (var cmd = new OdbcCommand())
{
cmd.Connection = con;
cmd.CommandText =
"SELECT COUNT(*) AS n FROM [VirtualMaster] " +
"WHERE StrComp([VirtualMonitorName],?,?) = 0";
cmd.Parameters.AddWithValue("?", "Vm1");
cmd.Parameters.Add("?", OdbcType.Int);
var vbCompareOptions = new Dictionary<string, int>()
{
{"vbBinaryCompare", 0},
{"vbTextCompare", 1}
};
string currentOption = "";
currentOption = "vbBinaryCompare";
cmd.Parameters[1].Value = vbCompareOptions[currentOption];
Console.WriteLine(
"{0} found {1} record(s)",
currentOption,
Convert.ToInt32(cmd.ExecuteScalar()));
currentOption = "vbTextCompare";
cmd.Parameters[1].Value = vbCompareOptions[currentOption];
Console.WriteLine(
"{0} found {1} record(s)",
currentOption,
Convert.ToInt32(cmd.ExecuteScalar()));
}
}
产生
vbBinaryCompare found 1 record(s)
vbTextCompare found 2 record(s)
这篇关于如何编写区分大小写的MS Access查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!