posts - 22, comments - 5, trackbacks - 0, articles - 11

Access,MS SQL Server 和 Oracle 中的通配符

Posted on 2009-03-03 18:39 疯狂水车 阅读(1127) 评论(0)  编辑 收藏 引用 所属分类: 转载

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 对话框来查找通配符本身时,只 需在 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 “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)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 '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 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-.15 off')GOSELECT c1 FROM mytbl2WHERE c1 LIKE '%10-15!% off%' ESCAPE '!'GO使用 [ ] 通配符的例子
    下例查找名字为 Cheryl 或 Sheryl 的作者。
    USE pubsGOSELECT au_lname, au_fname, phoneFROM authorsWHERE au_fname LIKE '[CS]heryl'ORDER BY au_lname ASC, au_fname ASCGO下例查找姓为 Carson、Carsen、Karson 或 Karsen 的作者所在的行。
    USE pubsGOSELECT au_lname, au_fname, phoneFROM authorsWHERE au_lname LIKE '[CK]ars[eo]n'ORDER BY au_lname ASC, au_fname ASCGOOracle 中通配符的使用情况
    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'
    只有注册用户登录后才能发表评论。