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。 | [ ] | 匹配方括号里的任意单个字符。 | B[ae]ll 可查到 ball 和 bell,但不能查到 bill。 | ! | 匹配任意不在方括号里的字符。 | b[!ae]ll 可查到 bill 和 bull,但不能查到 ball 或 bell。 | - | 匹配某个字符范围内的任意一个字符,字符范围必须以升序方式表示 (即 A 到 Z,不能为 Z 到 A)。 | b[a-c]d 可查到 bad,bbd 和 bcd。 | # | 匹配任意单个的数字。 | 1#3 可查到 103,113,123 | ANSI-92 通配符集
通配符 | 描述 | 示例 |
---|
% | 匹配任意数量的字符。可以写在字符串的开头和结尾。 | wh% 可查到 what,white 和 why,但不能查到 awhile 或 watch。 | _(下划线) | 匹配任意单个字符。 | B_ll 可查到 ball,bell和 bill。 | [ ] | 匹配方括号里的任意单个字符。 | B[ae]ll 可查到 ball 和 bell,但查不到 bill。 | ^ | 匹配不在方括号里的任意字符。 | b[^ae]ll 可查到 bill 和 bull,但查不到 ball 或 bell。 | -(连线) | 匹配某个字符范围内的任意一个字符,字符范围必须以升序方式表示 (即 A 到 Z,不能为 Z 到 A)。 | b[a-c]d 可查到 bad,bbd 和 bcd。 | 注意
可以用通配符搜索的数据类型 在某些情况下,我们在 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 | 依赖于源字段的数据类型。 | 注意
用包含通配符的查询语句(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 “P[A-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-P]arsen' 将查找以arsen 结尾且以介于 C 与 P 之间的任何单个字符开始的作者姓氏,例如,Carsen、Larsen、Karsen 等。 | [^] | 不属于指定范围 ([a-f]) 或集合 ([abcdef]) 的任何单个字符。 | WHERE au_lname LIKE 'de[^l]%' 将查找以 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)
AS
SELECT @AU_LNAME = RTRIM(@AU_LNAME) + '%'
SELECT t.title_id, t.title
FROM authors a, titleauthor ta, titles t
WHERE 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 pubs
GO
CREATE PROCEDURE find_books2 @au_lname varchar(20)
AS
SELECT t.title_id, t.title
FROM authors a, titleauthor ta, titles t
WHERE 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 column
CREATE 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 column
CREATE 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 RTRIM
CREATE 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 'abc[def]' | 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 pubs
GO
SELECT notes
FROM titles
WHERE notes LIKE '50%% off when 100 or more copies are purchased'
ESCAPE '%'
GO 下例使用 ESCAPE 子句和转义符查找 mytbl2 表的 c1 列中的精确字符串 10- 15%。 USE pubs
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'mytbl2')
DROP TABLE mytbl2
GO
USE pubs
GO
CREATE TABLE
mytbl2
(
c1 sysname
)
GO
INSERT mytbl2 VALUES ('Discount is 10-15% off')
INSERT mytbl2 VALUES ('Discount is .10-.15 off')
GO
SELECT c1
FROM mytbl2
WHERE c1 LIKE '%10-15!% off%' ESCAPE '!'
GO 使用 [ ] 通配符的例子 下例查找名字为 Cheryl 或 Sheryl 的作者。 USE pubs
GO
SELECT au_lname, au_fname, phone
FROM authors
WHERE au_fname LIKE '[CS]heryl'
ORDER BY au_lname ASC, au_fname ASC
GO 下例查找姓为 Carson、Carsen、Karson 或 Karsen 的作者所在的行。 USE pubs
GO
SELECT au_lname, au_fname, phone
FROM authors
WHERE au_lname LIKE '[CK]ars[eo]n'
ORDER BY au_lname ASC, au_fname ASC
GO Oracle 中通配符的使用情况 You use the LIKE operator to compare a character, string, or CLOB value to a pattern. Case is significant. LIKE returns the Boolean value TRUE if the patterns match or FALSE if they do not match. The patterns matched by LIKE can include two special-purpose characters called wildcards. An underscore (_ ) matches exactly one character; a percent sign (% ) matches zero or more characters. For example, if the value of ename is 'JOHNSON' , the following expression is true: ename LIKE 'J%SON' |