通配符AccessMSSQLServer和Oracle中的通配符

Acces sMS SQL Serve r和Oracl e中使用的通配符如果有什么不同主要就是因为支持的SQL规范不同。 SQL语言发布过两个规范ANSI-89和ANSI-92。跟ANSI-89比起来ANSI-92 SQL有新的和不同的保留关键字语法规则和通配符(Wildc ard)。虽然是两个规范并不表示支持哪个都好ANSI-92实际上是要替换ANSI-89的正如ANS I-89替换了之前并不广为所知的ANSI X3.135-1986。

Acces sMS SQL和Oracl e发展到今天也不知道经过多少版本了(遑论其它非主流的数据库系统)支持的SQL规范当然也是与时俱进。有时候我们说Acces s中的通配符和MSSQL和Oracl e不一样实际上是在拿一个比较老的Acces s版本和一个比较新的MSSQL作比较 因为支持最新的SQL规范是数据库系统程序发展的一个趋势没道理对新规范视而不见失去客户和市场吧(可以参照S ONY在MP 3规格上的失利)。换而言之Acces sMS SQL和Oracl e中的SQL语法将来应该是趋向一致的没有那么大的差异。其实 Acces s2003就已经向ANSI-92 SQL靠拢了。

除了支持的 SQL规范不同还有些不同就是开发商自己定义的那部分了。对SQL规范的实现或者这里减一点或者那里加一点没有说一定要忠实某个标准。不过对用户来说切换产品的时候就有必要了解都有哪些不同点和共同点还不能做到完全无缝衔接。比如Micro soft的Jet SQL虽然是参照 ANSI标准的到底夹带了很多自己的东西保留关键字和数据类型都不一样可以参考以下链接 Equiv alent ANSI SQL Data Types Microsoft Jet Datab ase Engine SQL Reserved Words Microsoft Jet Database Engine SQL Data TypesAcces s中查找处理数据的方法基本上可以分为两大类点Edit菜单调出来的Find andRepla ce对话框和用SQL语句编写的选择或动作查询。在Acces s2003数据库(.mdb)文件中 Find and Repla ce对话框和SQL语句编写的查询使用的规范是一致的安装时的默认项是ANSI-89规范我们可以自定义为ANSI-92规范。在Acces s2003版程序中我们可以配置数据库文件支持任意一种规范。但在Acces s2000版程序中只能用ANSI-89。ANSI-89通配符集通配符描述例子

通配符描述例子

*匹配任意数量的字符。星号可以写在字符串的任意位置。 wh*可以查到whatwhite和why但查不到awhil e或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和bc d。

#匹配任意单个的数字。 1#3可查到103 113 123

ANSI-92通配符集通配符描述示例

%匹配任意数量的字符。可以写在字符串的开头和结尾。 wh%可查到what white和why但不能查到 awhil e或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和bc d。

注意

要在数据中查找通配符本身用方括号把要找的字符括起来比如 [#] 。星号(*)问号(?)井号(#)开方括号( [)和连线号(-)都是如此。查找感叹号(!)或闭方括号 (])时不要用方括号括起来。用Find and Repla ce对话框来查找通配符本身时只需在Find What文本框里输入字符不要用方括号括起来。用查询来做也是如此例如Like "*!*"返回包含感叹号的所有记录。可以参考以下链接:

Find wildcard characters in an Access database.

如果你同时找连线号和其它字符在方括号里把连线号放在所有其它字符的前面或后面例如[-#*]或[#*-] 。但是 如果在开方括号后面有感叹号的话要把连线号放在感叹号后面[!-] 。

要查找开闭一对方括号 ([])必须要把两个符号都放在方括号里像这个样子 [[]] 。因为只写一对方括号的话Acces s会把它理解为零长字符串。要输入零长字符串打上两个双引号 中间不能有空格。

可以用通配符搜索的数据类型

在某些情况下我们在Find and Repla ce对话框中可以使用通配符但在查询语句中不能用

。反之亦然。

数据类型通配符用在„„

Text Find and Repla ce对话框查询语句

Memo Find and Repla ce对话框查询语句

Numbe rFind and Repla ce对话框查询语句

Date/Time Find and Repla ce对话框查询语句注意区域设置会影响通配符的使用请看下面的信息。

Curre ncy Find and Repla ce对话框查询语句

AutoN umber Find and Repla ce对话框查询语句

OLE Objec t无

Yes/No查询语句。但是不需要用通配符请看下面的信息。

Hyper link Find and Repla ce对话框查询语句

Looku pWizar d依赖于源字段的数据类型。

注意

日期/时间(Date/Time)类型的字段应用的格式如果能把时间以文本的方式显示出来我们就能凭我们看到的在Find and Repla ce对话框里输入通配符进行查找。我们不用关心这个类型实际存储的数值通配符匹配的是我们看到的东西这也就是Searc hField sas Forma tted

选项的意义默认是勾上的。还要记住的是控制面板里的区域设置会影响我们看到的格式和搜索的方式。比如 Acces s可能会把部分日期显示为罗马数字例如07-IX-1997表示07-Sept-1997。我们搜索的时候要以我们看到的为准则这是规律。例如我们输入

*-IX-1997就可以查到 1997年9月的所有记录。

另外如果日期字段显示的文本包含发音符号比如á或ä我们必须在准则字符串里包括这些发音符号,否则检索不会成功。我们可以用通配符绕过输入这些发音符号。例如有一个日期格式3-heinä-1997我们的查询字符串可以写成*-hein*-1997.

如果试图用 Find and Repla ce对话框搜索Yes/No字段 Acces s会禁用该字段对话框不返回任何结果。如果用查询语句搜索Yes/No字段则可以使用通配符。但记住Yes/No字段只返回两个值(0是fals e  -1是true)所以通配符对搜索并帮不上什么忙。例如准则=-1和"Lik e*1"返回的结果是一样的。

OLE Objec t字段不能查询。

用包含通配符的查询语句(selec t)搜索Date/Time字段

对Date/Time字段进行搜索的查询语句里可以使用通配符但要记住一个关键点跟Findand Repla ce对话框不一样查询不考虑格式的问题。

比如查询的准则写成Lik e"*10*" 。返回的结果有的看上去包含10比如10-J u l-1996但有的返回记录看上去不包含10比如03-Oct-1997。为什么呢因为在格利高里(Grego rian)历里 Octob er是第十个月 。当你在查询语句里使用通配符的时候Acces s是不管字段的格式的。我们可以把Date/Time字段的格式去掉后再比较一下查询的返回结果。通配符的最大用处是用 Like操作符进行模糊查询 Like操作符把表中字符串类型的字段跟某个模式进行比较。

语法 expre ssion Like “patte rn”

语法部分描述expre ssion SQL表达式往往出现在WHERE子句里也可以出现在Join On子句里。通常是表字段名或者是字段名作参数的函数。patte rn模式文本字符串表达式字符串跟它做比较。

说明

Lik e操作符可以在字段里找出匹配指定模式的值。模式可以是一个很明确的值(例如Lik e “S mith” )或者包含通配符来查找一系列值(例如 Like “S m*” ) .

在下面的例子中 比较结果返回P打头第二个字符是A-F中任意一个再跟着3个数字的数据。

Lik e“P[A-F]###”

下表列出Lik e操作符跟不同模式进行比较的结果

匹配类型模式匹配的值不匹配的值

多个字符a*a aa,aBa,aBBBa aBC

*ab*abc,AABB,Xab aZb,bac

特殊字符a[*]a a*a aaa

多个字符ab*abcde fg,abc cab,aab

单个字符a?a aaa,a3a,aBa aBBBa

单个数字a#a a0a,a1a,a2a aaa,a10a

在某个字符范围内[a-z]f,p, j2,&

不在某个字符范围内 [!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 Serve r2000及以上的通配符

SQL Serve r中Like操作符的语法跟Acces s中略有不同

语法match_expr essio n[NOT]LIKE patte rn[ESCAP Eescap e_cha racte r]

参数match_expr essio n任何字符串数据类型的有效SQL Serve r表达式。patte rn match_expr essio n中的搜索模式可以包含下列有效SQL Serve r通配符。 通配符描述示例

%包含零个或更多字符的任意字符串 。WHERE title LIKE'%compu ter%'将查找处于书名任意位置的包含单词compu ter的所有书名。

_下划线任何单个字符。WHERE au_fn ame LIKE'_ean'将查找以ean结尾的所有4个字母的名字Dean、 Sean等。

[ ]指定范围 ([a-f])或集合([abcde f])中的任何单个字符。 WHERE au_ln ame LIKE'[C-P]ars en'将查找以a rsen结尾且以介于C与P之间的任何单个字符开始的作者姓氏例如 Carse n、 Larse n、Karse n等。

[^]不属于指定范围([a-f])或集合([abcde f])的任何单个字符。 WHERE au_ln ame LIKE'de[^l]%'将查找以de开始且其后的字母不为 l的所有作者的姓氏。escap e_cha racte r字符串数据类型分类中的所有数据类型的任何有效SQL Serve r表达式。escap e_cha racte r没有默认值且必须仅包含一个字符。

结果类型Boole an

结果值如果match_expr essio n匹配指定模式 LIKE将返回TRUE。

注释

模式可以包含常规字符和通配符字符。模式匹配过程中常规字符必须与字符串中指定的字符完全匹配。然而可使用字符串的任意片段匹配通配符。与使用=和!=字符串比较运算符相比使用通配符可使LI KE运算符更加灵活。如果任何参数都不属于字符串数据类型 SQL Serve r会将其转换成字符串数据类型如果可能。

当使用LI KE进行字符串比较时模式字符串中的所有字符都有意义包括起始或尾随空格。如果查询中的比较要返回包含"abc " abc后有一个空格的所有行则将不会返回包含"abc"  abc 后没有空格的所在行。但是可以忽略模式所要匹配的表达式中的尾随空格。如果查询中的比较要返回包含"abc" abc后没有空格的所有行则将返回以"abc"开始且具有零个或多个尾随空格的所有行。

由于数据存储方式的原因数据类型是 char和varch ar的字符串可能无法通过 LIKE比较。 了解每种数据类型的存储方式以及导致LIKE比较失败的原因十分重要。下面的示例将局部 char变量传递给存储过程然后使用模式匹配查找某个作者的所有著作。在此过程中作者的姓将作为变量传递。

CREATE PROCEDURE find_books@AU_LNAME char(20)

AS

S ELEC T@AU_LN AME=RTRIM(@AU_LN AME)+'%'

SELEC T t.title_id, t.title

FROM authors a, titleauthor ta, title s t

WHERE a.au_id=ta.au_id AND ta.title_id=t.title_id

AND a.au_ln ame LIKE@AU_LN AME当名字中包含的字符数小于 20时 char变量(@AU_LN AME)将包含尾随空格这导致find_books过程中没有行返回。 由于au_ln ame列为varch ar类型所以没有尾随空格。因为尾随空格是有意义的所以此过程失败。但下面的示例是成功的 因为尾随空格没有被添加到varch ar变量中

USE pubs

GO

CREATE PROCEDURE find_books2@au_lname varchar(20)

AS

SELEC T t.title_id, t.title

FROM authors a, titleauthor ta, title s t

WHERE a.au_id=ta.au_id AND ta.title_id=t.title_id

AND a.au_lname LIKE@au_lname+'%'

EXEC find_books 2'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)affec ted)当搜索datet ime值时推荐使用LIKE因为datet ime项可能包含各种日期部分。例如如果将值19981231 9:20插入到名为 arriv al_ti me的列中则子句WHEREarriv al_ti me= 9:20将无法找到 9:20字符串的精确匹配 因为SQL Serve r将其转换为1900年1月 1 日上午9:20。然而子句WHERE arriv al_ti me LIKE'%9:20%'将找到匹配。

LIKE支持ASCII模式匹配和 Unico de模式匹配。当所有参数包括match_expr essio n、patte rn和escap e_cha racte r如果有都是ASCII字符数据类型时将执行ASCII模式匹配。如果其中任何参数属于 Unico de数据类型则所有参数将被转换为 Unico de并执行Unico de模式匹配。当对Unico de数据nchar或nvarc har数据类型使用LIKE时尾随空格是有意义的。但是对于非 Unico de数据尾随空格没有意义。 Unico de LIKE与SQL-92标准兼容。ASCII LIKE与SQL Serve r的早期版本兼容。

下面的一系列示例显示ASCII LIKE模式匹配与 Unico de LIKE模式匹配所返回的行之间的差异

--ASCII pattern matching with char column

CREATE TABLE t (col1 char(30))

INSERT INTO t VALUES('Robert King')

S ELEC T*

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')

S ELEC T*

FROM t

WHERE col1 LIKE'%King' --no rows returned--Unico de pattern matching with nchar columnand RTRIM

CREATE TABLE t (col1 nchar(30))

INSERT INTO t VALUES('Robert King')

S ELEC T*

FROM t

WHERE RTRIM(col1)LIKE'%King' -- retur ns 1 row说明 如果使用LIKE进行字符串比较模式字符串中的所有字符都有意义包括起始空格或尾随空格。

可以将通配符本身用作文字字符串方法是将通配符放在括号中。下表显示了 使用LIKE关键字和[ ]通配符的示例。

符号含义

LI KE'5[%]' 5%

LI KE'[_]n'_n

LI KE'[a-c df]'a、 b、 c、 d或f

LI KE'[-ac df]' -、 a、 c、 d或f

LI KE'[ [ ]' [

LI KE']' ]

LIKE'abc[_]d%'abc_d和abc_d e

LIKE'abc[def]'abc d、 abc e和abc f

使用ESCAP E子句的模式匹配

可搜索包含一个或多个特殊通配符的字符串。例如custo mers数据库中的 disco unts表可能存储含百分号(%)的折扣值。若要搜索作为常规字符而不是通配符的百分号必须提供ESCAP E关键字和转义符。例如一个样本数据库包含名为comme nt的列该列含文本30%。若要搜索在 comme nt列中的任何位置包含字符串 30% 的任何行请指定由WHERE comme nt LIKE'%30!%%'ESCAP E' !'组成的WHERE子句。如果不指定 ESCAPE和转义符 SQL Serve r将返回所有含字符串30的行。

下例说明如何在pubs数据库title s表的notes列中搜索字符串"50%off when 100 or morecopie s are purch ased"

USE pubs

GO

SELECT notes

FROM title s

WHERE notes LIKE'50%%off when 100 or more copies are purchased'

ESCAPE'%'

GO下例使用ESCAP E子句和转义符查找mytbl 2表的c 1列中的精确字符串10- 15%。USE pubs

GO

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE S

WHERE T ABLE_NAME='mytb l2')

DROP TABLE mytbl 2

GO

USE pubs

GO

CREATE TABLEmytb l 2

(c1 sysname

)

GO

INSERT mytbl2 VALUE S('Discount is 10-15%off')

INSERT mytbl2 VALUE S('Dis c ount is .10-.15 off')

GO

SELEC T c 1

FROM mytbl2

WHERE c 1 LIKE'%10-15!%off%'ESCAP E' !'

GO使用[ ]通配符的例子

下例查找名字为Chery l或Shery l的作者。

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、 Carse n、Karson或Karse n的作者所在的行。

USE pubs

GO

SELECT au_lname,au_fname,phone

FROM authors

WHERE au_ln ame LI KE'[CK]ars[eo]n'

ORDER BY au_lname ASC,au_fname ASC

GOOra cle中通配符的使用情况

You use the LIKE opera tor to compare a character, string, or CLOB value to a patte rn.Case issignificant.LIKE returns the Boole an value TRUE if the patte rns match or FALSE if they do notmatch.

The patterns matched by LIKE can inclu de two special-purpose characters called wildc ards.Anunderscore(_)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'

本文来自C SDN博客转载请标明出处http://blog.csdn.net/grace xu/archi ve/2008/04/18/23051

93.aspx