oracle 中使用like的問題
今天我想查詢開發人員建立主鍵時使用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中not like效率問題Oracle
- Oracle 解決like中無法匹配下劃線的問題Oracle
- Oracle中的like優化Oracle優化
- SQL "like" 運算子的問題SQL
- Laravel 框架中 whereRaw like 引數繫結問題Laravel框架
- 轉--oracle中替代LIKE方法Oracle
- 【Tip】解決like中無法匹配下劃線的問題
- MySQL 中文 like 問題解決MySql
- Oracle中rownum與rowid使用上的問題Oracle
- 問題:mybatis like 佔位符處理MyBatis
- openJPA 遭遇PG 之 like 語法問題
- MyBatis中Like語句使用總結MyBatis
- SQL中LIKE的妙用SQL
- oracle使用者口令中帶@符號問題Oracle符號
- laravel使用中遇到的問題Laravel
- Oracle索引失效-likeOracle索引
- oracle正規表示式regexp_like的使用Oracle
- Oracle中like效率正規表示式淺析Oracle
- EXCEl中LIKE的實現Excel
- hibernate中oracle的問題,,急啊?Oracle
- Oracle SQL Like 的最佳化OracleSQL
- PHP中include()的使用問題 (轉)PHP
- Typora 使用中的幾個問題
- Oracle10g EM使用中的主機身份證明問題!Oracle
- oracle自帶的sql developer使用問題OracleSQLDeveloper
- Oracle中的Session kill不釋放問題OracleSession
- Vue使用中遇到的程式碼問題Vue
- Linux ~ CentOS使用中遇到的問題LinuxCentOS
- CKEditor使用中遇到的問題解決
- Fragstas軟體使用中遇到的問題
- [丹臣]ORACLE中Like與Instr效能大比拼Oracle
- 使用ADO訪問Oracle問題彙總Oracle
- oracle sql like優化(一)OracleSQL優化
- PHP中Autoload使用中的一些問題PHP
- Oracle 12c PDB中碰到的DG問題Oracle
- 使用tar安裝RAC系統中的節點問題 - tar ORACLE_HOMEOracle
- java Gson使用中遇到的Date格式問題Java
- sybase中cursor的使用中死迴圈問題解決