AccessMSSQLServer和Oracle中的通配符.doc

Access，MS SQL Server 和 Oracle 中使用的通配符如果有什么不同，主要就是因为支持的 SQL 规范不同。SQL 语言发 布过两个规范：ANSI-89 和 ANSI-92。跟 ANSI-89 比起来，ANSI-92 SQL 有新的和不同的保留关键字，语法规则和通配符(Wildcard)。虽然是两个规范，并不表示支持哪个都好，ANSI-92 实际上是要替换 ANSI-89 的，正如ANSI-89 替换了之前并不广为所知的 ANSI X3.135-1986。Access，MS SQL 和 Oracle 发展到今天也不知道经过多少版本了(遑论其它非主流的数据库系统)，支持的 SQL 规 范当然也是与时俱进。有时候，我们说 Access 中的通配符和 MS SQL，和 Oracle 不一样，实际上是在拿一个比较老的 Access 版本和一个比较新的 MS SQL 作比较，因为支持最新的 SQL 规范是数据库系统程序发展的一个趋势，没道理对新规范视而不见，失去客户和市场吧(可以参照 SONY 在 MP3 规格上的失利)。换而言之，Access，MS SQL 和 Oracle 中的 SQL 语法将来应该是趋向一致的，没有那么大的差异。其实，Access 2003 就已经向 ANSI-92 SQL 靠拢了。除了支持的 SQL 规范不同，还有些不同就是开发商自己定义的那部分了。对 SQL 规范的实现，或者这里减一点，或者那里加一点，没有说一定要忠实某个标准。不过对用户来说，切换产品的时候就有必要了解都有哪些不同点和共同点，还不能做到完全无缝衔接。比如 Microsoft 的 Jet SQL，虽然是参照 ANSI 标准的，到底夹带了很多自己的东西，保留关键字和数据类型都不一样，可以参考以下链接： Equivalent ANSI SQL Data Types Microsoft Jet Database Engine SQL Reserved Words Microsoft Jet Database Engine SQL Data Types Access 中查找处理数据的方法基本上可以分为两大类：点 Edit 菜单调出来的 Find and Replace 对话框；和用 SQL 语句编写的选择或动作查询。在 Access 2003 数据库(.mdb) 文件中， Find and Replace 对话框和 SQL 语句编写的查询使用的规范是一致的，安装时的默认项是 ANSI-89 规范，我们可以自定义为 ANSI-92 规范。在 Access 2003 版程序中，我们可以配置数据库文件支持任意一种规范。但在 Access 2000 版程序中，只能用 ANSI-89。 ANSI-89 通配符集 通配符 描述 例子 通配符 描述 例子 * 匹配任意数量的字符。星号可以写在字符串的任意位置。 wh* 可以查到 what，white 和 why，但查不到 awhile 或 watch。 ? 匹配任意单个的字符。 B?ll 可查到 ball，bell 和 bill。 匹配方括号里的任意单个字符。 Baell 可查到 ball 和 bell，但不能查到 bill。 ! 匹配任意不在方括号里的字符。 b!aell 可查到 bill 和 bull，但不能查到 ball 或 bell。 - 匹配某个字符范围内的任意一个字符，字符范围必须以升序方式表示 (即 A 到 Z，不能为 Z 到 A)。 ba-cd 可查到 bad，bbd 和 bcd。 # 匹配任意单个的数字。 1#3 可查到 103，113，123 ANSI-92 通配符集 通配符 描述 示例 % 匹配任意数量的字符。可以写在字符串的开头和结尾。 wh% 可查到 what，white 和 why，但不能查到 awhile 或 watch。 _(下划线) 匹配任意单个字符。 B_ll 可查到 ball，bell和 bill。 匹配方括号里的任意单个字符。 Baell 可查到 ball 和 bell，但查不到 bill。 匹配不在方括号里的任意字符。 baell 可查到 bill 和 bull，但查不到 ball 或 bell。 -(连线) 匹配某个字符范围内的任意一个字符，字符范围必须以升序方式表示 (即 A 到 Z，不能为 Z 到 A)。 ba-cd 可查到 bad，bbd 和 bcd。 注意要在数据中查找通配符本身，用方括号把要找的字符括起来，比如 # 。星号(*)，问号(?)，井号(#)，开方括号( ) 和连线号(-) 都是如此。查找感叹号(!) 或闭方括号 ()时不要用方括号括起来。用 Find and Replace 对话框来查找通配符本身时，只 需在 Find What 文本框里输入字符，不要用方括号括起来。用查询来做也是如此，例如 Like *!* 返回包含感叹号的所有记录。可以参考以下链接: Find wildcard characters in an Access database.如果你同时找连线号和其它字符，在方括号里把连线号放在所有其它字符的前面或后面，例如 -#* 或 #*-。但是， 如果在开方括号后面有感叹号的话，要把连线号放在感叹号后面 !-。 要查找开闭一对方括号 ()，必须要把两个符号都放在方括号里，像这个样子 。因为只写一对方括号的话，Access 会把它理解为零长字符串。要输入零长字符串，打上两个双引号，中间不能有空格。 可以用通配符搜索的数据类型 在某些情况下，我们在 Find and Replace 对话框中可以使用通配符，但在查询语句中不能用。反之亦然。数据类型 通配符用在 Text Find and Replace 对话框，查询语句 Memo Find and Replace 对话框，查询语句 Number Find and Replace 对话框，查询语句 Date/Time Find and Replace 对话框，查询语句 注意：区域设置会影响通配符的使用，请看下面的信息。 Currency Find and Replace 对话框，查询语句 AutoNumber Find and Replace 对话框，查询语句 OLE Object 无 Yes/No 查询语句。但是不需要用通配符，请看下面的信息。 Hyperlink Find and Replace 对话框，查询语句 Lookup Wizard 依赖于源字段的数据类型。 注意日期/时间(Date/Time)类型的字段应用的格式如果能把时间以文本的方式显示出来，我们就能凭我们看到的在 Find and Replace 对话框里输入通配符进行查找。我们不用关心这个类型实际存储的数值，通配符匹配 的是我们看到的东西，这也就是 Search Fields as Formatted 选项的意义，默认是勾上的。还要记住的是，控制面板里的区域设置会影响我们看到的格式和搜索的方式。比如，Access 可能会把部分日期显示为罗马数字，例如 07-IX-1997，表示 07-Sept-1997。我们搜索的时候，要以我们看到的为准则，这是规律。例如，我们输入 *-IX-1997 就可以查到 1997年 9月的所有记录。 另外，如果日期字段显示的文本包含发音符号，比如 或 ， 我们必须在准则字符串里包括这些发音符号,否则检索不会成功。我们可以用通配符绕过输入这些发音符号。例如，有一个日期 格式 3-hein-1997，我们的查询字符串可以写成 *-hein*-1997. 如果试图用 Find and Replace 对话框搜索 Yes/No 字段，Access 会禁用该字段，对话框不 返回任何结果。如果用查询语句搜索 Yes/No 字段，则可以使用通配符。但记住，Yes/No 字段只返回两个值(0 是 false，-1 是 true)，所以通配符对搜索并帮不上什么忙。例如，准则 =-1 和 Like *1返回的结果是一样的。 OLE Object 字段不能查询。 用包含通配符的查询语句(select)搜索 Date/Time 字段 对 Date/Time 字段进行搜索的查询语句里可以使用通配符，但要记住一个关键点：跟 Find and Replace 对话框不一样，查询不考虑格式的问题。比如查询的准则写成 Like *10* 。返回的结果有的看上去包含 10，比如 10-Jul-1996； 但有的返回记录看上去不包含 10，比如 03-Oct-1997。为什么呢？因为在格利高里(Gregorian)历里，October 是第十个月 。当你在查询语句里使用通配符的时候，Access 是不管字段的格式的。我们可以把 Date/Time 字段的格式去掉后再比较一下查询的返回结果。 通配符的最大用处是用 Like 操作符进行模糊查询，Like 操作符把表中字符串类型的字段跟某个模式进行比较。 语法： expression Like “pattern”语法部分 描述 expression SQL 表达式，往往出现在 WHERE 子句里，也可以出现在 Join On 子句里。通常是表字段名 ，或者是字段 名作参数的函数。 pattern 模式文本字符串，表达式字符串跟它做比较。 说明： Like 操作符可以在字段里找出匹配指定模式的值。模式可以是一个很明确的值 (例如， Like “Smith”)，或者包含通配符来查找一系列值 (例如，Like “Sm*”) .在下面的例子中，比较结果返回 P 打头，第二个字符是 A-F 中任意一个，再跟着 3 个数字的数据。Like “PA-F#”下表列出 Like 操作符跟不同模式进行比较的结果：匹配类型 模式 匹配的值 不匹配的值 多个字符 a*a aa, aBa, aBBBa aBC *ab* abc, AABB, Xab aZb, bac 特殊字符 a*a a*a aaa 多个字符 ab* abcdefg, abc cab, aab 单个字符 a?a aaa, a3a, aBa aBBBa 单个数字 a#a a0a, a1a, a2a aaa, a10a 在某个字符范围内 a-z f, p, j 2, & 不在某个字符范围内 !a-z 9, &, % b, a 不是数字 !0-9 A, a, &, 0, 1, 9 综合 a!b-m# An9, az0, a99 abc, aj0 模式匹配的注意事项：连线号(-)出现在模式字符串最前面或最后面的位置用来匹配它自己(前面允许出现感叹号!)。出现在其它位置时则用 来表 示 ANSI 字符集的范围。 指定字符范围时，一定要用升序 (A-Z 或 0-100)。A-Z 是有效的，但 Z-A 则是无效的。 被认为是零长字符串()。 MS SQL Server 2000 及以上的通配符SQL Server 中 Like 操作符的语法跟 Access 中略有不同： 语法 match_expression NOT LIKE pattern ESCAPE escape_character 参数 match_expression 任何字符串数据类型的有效 SQL Server 表达式。 pattern match_expression 中的搜索模式，可以包含下列有效 SQL Server 通配符。 通配符 描述 示例 % 包含零个或更多字符的任意字符串。 WHERE title LIKE %computer% 将查找处于书名任意位置的包含单词 computer 的所有 书名。 _（下划线） 任何单个字符。 WHERE au_fname LIKE _ean 将查找以 ean 结尾的所有 4 个字母的名字（Dean、 Sean 等）。 指定范围 (a-f) 或集合 (abcdef) 中的任何单个字符。 WHERE au_lname LIKE C-Parsen 将查找以arsen 结尾且以介于 C 与 P 之间的任何单个字符开始的作者姓氏，例如，Carsen、Larsen、Karsen 等。 不属于指定范围 (a-f) 或集合 (abcdef) 的任何单个字符。 WHERE au_lname LIKE del% 将查找以 de 开始且其后的字母不为 l 的所有作者的姓氏。 escape_character 字符串数据类型分类中的所有数据类型的任何有效 SQL Server 表达式。 escape_character 没有默认值，且必须仅包含一个字符。 结果类型 Boolean 结果值 如果 match_expression 匹配指定模式，LIKE 将返回 TRUE。 注释模式可以包含常规字符和通配符字符。模式匹配过程中，常规字符必须与字符串中指定的字符完全匹配。然而，可使用 字符串的任意片段匹配通配符。与使用 = 和 != 字符串比较运算符相比，使用通配符可 使 LIKE 运算符更加灵活。如果任何 参数都不属于字符串数据类型，SQL Server 会将其转换成字 符串数据类型（如果可能）。 当使用 LIKE 进行字符串比较时，模式字符串中的所有字符都有意义，包括起始或尾随空格。如果查询中的比较要返回包含 abc （abc 后有一个空格）的所有行，则将不会返回包含abc（abc 后没有空格）的所在行。但是可以忽略模式所要匹配的表达式中的尾随空格。如果查询中的比较要返回包含abc（abc 后没有空格）的所有行，则将返回以abc开始且具有零个或多个尾随空格的所有行。由于数据存储方式的原因，数据类型是 char 和 varchar 的字符串可能无法通过 LIKE 比较。了解每种数据类型的存储方式以及导致 LIKE 比较失败的原因十分重要。下面的示例将局部 char 变量传递给存储过程，然后使用模式匹配查找某个作者的所有著作。在此过程中，作者的姓将作为变量传递。CREATE PROCEDURE find_books AU_LNAME char(20)ASSELECT AU_LNAME = RTRIM(AU_LNAME) + %SELECT t.title_id, t.title FROM authors a, titleauthor ta, titles tWHERE a.au_id = ta.au_id AND ta.title_id = t.title_id AND a.au_lname LIKE AU_LNAME当名字中包含的字符数小于 20 时，char 变量 (AU_LNAME) 将包含尾随空格，这导致 find_books 过程中没有行返回。由于 au_lname 列为 varchar 类型，所以没有尾随空格。因为尾随空格是有意义的，所以此过程失败。 但下面的示例是成功的，因为尾随空格没有被添加到 varchar 变量中：USE pubsGOCREATE PROCEDURE find_books2 au_lname varchar(20)ASSELECT t.title_id, t.title FROM authors a, titleauthor ta, titles tWHERE a.au_id = ta.au_id AND ta.title_id = t.title_id AND a.au_lname LIKE au_lname + % EXEC find_books2 ring下面是结果集： title_id title - -MC3021 The Gourmet Microwave PS2091 Is Anger the Enemy? PS2091 Is Anger the Enemy? PS2106 Life Without Fear(4 row(s) affected)当搜索 datetime 值时，推荐使用 LIKE，因为 datetime 项可能包含各种日期部分。例如，如果将值 19981231 9:20 插入到名为 arrival_time 的列中，则子句 WHERE arrival_time = 9:20 将无法找到 9:20 字符串的精确匹配，因为 SQL Server 将其转换为 1900 年 1 月 1 日 上午 9:20。然而，子句 WHERE arrival_time LIKE %9:20% 将找到匹配。 LIKE 支持 ASCII 模式匹配和 Unicode 模式匹配。当所有参数，包括 match_expression、pattern 和 escape_character（如果有）都是 ASCII 字符数据类型时，将执行 ASCII 模式匹配。如果其中任何参数属于 Unicode 数据类型，则所有参数将被转换为 Unicode 并执行 Unicode 模式匹配。当对 Unicode 数据 （nchar 或 nvarchar 数据类型）使用 LIKE 时，尾随空格是有意义的。但是对于非 Unicode 数据，尾随空格没有意义。Unicode LIKE 与 SQL-92 标准兼容。ASCII LIKE 与 SQL Server 的早期版本兼容 。下面的一系列示例显示 ASCII LIKE 模式匹配与 Unicode LIKE 模式匹配所返回的行之间的差异：- ASCII pattern matching with char columnCREATE TABLE t (col1 char(30)INSERT INTO t VALUES (Robert King)SELECT * FROM t WHERE col1 LIKE % King - returns 1 row- Unicode pattern matching with nchar columnCREATE TABLE t (col1 nchar(30)INSERT INTO t VALUES (Robert King)SELECT * FROM t WHERE col1 LIKE % King - no rows returned- Unicode pattern matching with nchar column and RTRIMCREATE TABLE t (col1 nchar (30)INSERT INTO t VALUES (Robert King)SELECT * FROM t WHERE RTRIM (col1) LIKE % King - returns 1 row说明 如果使用 LIKE 进行 字符 串比较，模式字符串中的所有字符都有意义，包括起始空格或尾随空格。可以将通配符本身用作文字字符串，方法是将通配符放在括号中。下表显示了使用 LIKE 关键字和 通 配符 的示例。符号 含义 LIKE 5% 5% LIKE _n _n LIKE a-cdf a、b、c、d 或 f LIKE -acdf -、a、c、d 或 f LIKE LIKE LIKE abc_d% abc_d 和 abc_de LIKE abcdef abcd、abce 和 abcf 使用 ESCAPE 子句的模式匹配 可搜索包含一个或多个特殊通配符的字符串。例如，customers 数据库中的 discounts 表可能存储含百分号 (%) 的折扣值。若要搜索作为常规字符而不是通配符的百分号，必须提供 ESCAPE 关键字和转义符 。例如，一个样本数据库包含名为 comment 的列，该列含文本 30%。若要搜索在 comment 列中的任何位置包含字符串 30% 的任何行，请指定由 WHERE comment LIKE %30!% ESCAPE ! 组成的 WHERE 子句。如果不指定 ESCAPE 和转义符，SQL Server 将返回所有含字符串 30 的行。下例说明如何在 pubs 数据库 titles 表的 notes 列中搜索字符串50% off when 100 or more copies are purchased：USE pubsGOSELECT notesFROM titlesWHERE notes LIKE 50% off when 100 or more copies are purchased ESCAPE %GO下例使用 ESCAPE 子句和转义符查找 mytbl2 表的 c1 列中的精确字符串 10- 15%。USE pubsGOIF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = mytbl2) DROP TABLE mytbl2GOUSE pubsGOCREATE TABLE mytbl2( c1 sysname)GOINSERT mytbl2 VALUES (Discount is 10-15% off)INSERT mytbl2 VALUES (Discount is .10