ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 中使用like的问题

oracle 中使用like的问题

原创 Linux操作系统 作者:lfree 时间:2006-08-10 00:00:00 0 删除 编辑

今天我想查找开发人员建立主键时使用PK__开头的索引,发现执行如下语句会查询不是两个PK__开头的语句:

SELECT *
FROM dba_indexes
WHERE owner = 'ICARE' AND index_name LIKE 'PK__%'
ORDER BY 2

很明显,_在like中表示任意字符,但是如何使用转义呢?

google查询了一下发现如下链接记录下来:

http://www-eleves-isia.cma.fr/documentation/OracleDoc/LIKE-OPERATOR.html

但愿\在blog中正常,修改为如下,执行就正确了:

SELECT *
FROM dba_indexes
WHERE owner = &o AND index_name LIKE 'PK__%' ESCAPE ''
ORDER BY 2


LIKE-OPERATOR
    LIKE OPERATOR 

The LIKE operator is used in character string comparisons with
pattern matching. The syntax for a condition using the LIKE
operator is shown in this diagram:

SYNTAX:

char1 [NOT] LIKE char2 [ESCAPE 'c']

where:

char1
is a value to be compared with a pattern. This value can have
datatype CHAR or VARCHAR2.

NOT
logically inverts the result of the condition, returning FALSE if
the condition evaluates to TRUE and TRUE if it evaluates to FALSE.

char2
is the pattern to which char1 is compared. The pattern is a value
of datatype CHAR or VARCHAR2 and can contain the special pattern
matching characters % and _.

ESCAPE
identifies a single character as the escape character. The escape
character can be used to cause Oracle to interpret % or _ literally,
rather than as a special character, in the pattern.
If you wish to search for strings containing an escape character, you
must specify this character twice. For example, if the escape
character is '/', to search for the string 'client/server', you must
specify, 'client//server'.

While the equal (=) operator exactly matches one character value to
another, the LIKE operator matches a portion of one character value
to another by searching the first value for the pattern specified by
the second.

With the LIKE operator, you can compare a value to a pattern rather
than to a constant. The pattern can only appear after the LIKE
keyword. For example, you can issue the following query to find the
salaries of all employees with names beginning with 'SM':

SELECT sal
FROM emp
WHERE ename LIKE 'SM%'

The following query finds the salaries of all employees with the
name 'SM%', since the query uses the equality operator instead of
the LIKE operator:

SELECT sal
FROM emp
WHERE ename = 'SM%'

The following query finds the salaries of all employees with the
name 'SM%'. Oracle interprets 'SM%' as a text literal, rather than
as a pattern, because it precedes the LIKE operator:

SELECT sal
FROM emp
WHERE 'SM%' LIKE ename

Patterns often use special characters that Oracle matches with
different characters in the value:

* An underscore (_) in the pattern matches exactly one character (as
opposed to one byte in a multi-byte character set) in the value.
* A percent sign (%) in the pattern can match zero or more
characters (as opposed to bytes in a multi-byte character set) in
the value. Note that the pattern '%' cannot match a null.

Case is significant in all conditions comparing character
expressions including the LIKE and equality (=) operators. You can
use the UPPER() function to perform a case insensitive match, as in
this condition:

UPPER(ename) LIKE 'SM%'

When LIKE is used to search an indexed column for a pattern, the
performance benefit associated with the index is lost if the first
character in the pattern is % or _. If the leading character in the
pattern is not % or _, there is some performance benefit to the
index because Oracle can restrict the comparison to rows known to
begin with the specified first character.

EXAMPLE I:
This condition is true for all ENAME values beginning with MA:

ename LIKE 'MA%'

All of these ENAME values make the condition TRUE:

MARTIN, MA, MARK, MARY

Since case is significant, ENAME values beginning with Ma, ma, and
mA make the condition FALSE.

EXAMPLE II:
Consider this condition:

ename LIKE 'SMITH_'

This condition is true for these ENAME values:

SMITHE, SMITHY, SMITHS

This condition is false for 'SMITH', since the special character _
must match exactly one character of the ENAME value.

THE ESCAPE OPTION:
You can include the actual characters % or _ in the pattern by using
the ESCAPE option. The ESCAPE option identifies the escape
character. If the escape character appears in the pattern before
the character % or _, Oracle interprets this character literally in
the pattern, rather than as a special pattern matching character.

EXAMPLE III:
To search for any employees with the character string 'A_B' in their
name:

SELECT ename
FROM emp
WHERE ename LIKE '%A_B%' ESCAPE ''

The ESCAPE option identifies the backslash () as the escape
character. In the pattern, the escape character precedes the
underscore (_). This causes Oracle to interpret the underscore
literally, rather than as a special pattern matching character.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-82895/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6292307