`
zgqynx
  • 浏览: 1371239 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

oracle like

阅读更多
http://www.adp-gmbh.ch/ora/sql/like.html

http://www.techonthenet.com/sql/like.php

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

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.
分享到:
评论

相关推荐

    oracle like 的优化

    ### Oracle LIKE 语句优化详解 #### 一、引言 在Oracle数据库中,LIKE操作符是用于模糊查询的关键字之一。它允许用户通过模式匹配的方式检索数据,从而增加了查询的灵活性。然而,这种灵活性也带来了性能上的挑战...

    Oracle如何实现like多个值的查询

    在Oracle数据库中,当需要对某个字段执行`LIKE`操作以匹配多个可能的值时,通常会遇到效率和性能的问题。在上述问题中,客户的需求是根据一系列不规范的电话号码查询通话记录。传统的`LIKE`操作对于大量数据和多条件...

    Oracle如何对CLOB行字段来执行全文检索

    然而,在 Oracle8i 版本之前,对大字段 CLOB 仍然不支持在 where 子句直接的 Like 操作。这使得开发者无法直接对 CLOB 字段中的内容进行 Like 查找。幸运的是,通过使用 DBMS_LOB 包,我们可以实现对 CLOB 字段中的...

    Oracle中Like与Instr模糊查询性能大比拼

    在Oracle数据库中,进行字符串匹配查询时,我们经常会遇到`LIKE`和`INSTR`这两个操作符。它们在处理模糊查询时各有特点,但性能上可能存在显著差异。本篇文章将探讨`LIKE`与`INSTR`在实际应用中的性能表现,并提供...

    oracle_10g正则表达式_REGEXP_LIKE_用法

    下面将详细介绍`REGEXP_LIKE`的用法以及如何在Oracle 10g中使用正则表达式进行数据筛选。 ### Oracle 10g正则表达式与`REGEXP_LIKE` Oracle 10g支持多种正则表达式相关的函数,包括但不限于: 1. **`REGEXP_LIKE`...

    oracle使用instr或like方法判断是否包含字符串

    首先想到的就是contains,contains用法如下: select * from students where contains(address, 'beijing') 但是,使用contains谓词有...另外,还有个笨办法,那就是使用like,说到这里大家应该知道怎么做了吧: sel

    Mybatis 中 Oracle 的拼接模糊查询及用法详解

    Mybatis 中 Oracle 的拼接模糊查询及用法详解 Mybatis 是一个基于 Java 的持久层框架,提供了强大的数据库交互能力,而 Oracle 则是业界最流行的关系数据库管理系统。本文将详细介绍 Mybatis 中 Oracle 的拼接模糊...

    Oracle分析函数基本概念和语法总结及Regexp_***用法

    Oracle分析函数是数据库管理系统Oracle中的一种高级查询工具,它们用于处理行集,计算基于特定窗口内的数据的聚合值。分析函数的主要特点是返回的结果不仅仅是一行,而是多行,这与传统的聚合函数(如SUM, AVG等)...

    oracle正则表达式regexp_like的用法详解

    在Oracle数据库中,regexp_like是一个非常有用的函数,用于实现正则表达式匹配,其功能类似于LIKE,但提供了更为强大的模式匹配能力。在深入了解regexp_like函数之前,有必要先了解一些基础知识点,包括正则表达式的...

    数据库面试题4 oracle笔试 oracle例题

    - 使用`LIKE`进行模糊匹配时,若通配符`%`出现在搜索词的开头,如`last_name LIKE '%cliton%'`,Oracle不会使用索引,导致全表扫描。但若`%`在字符串中间或末尾,如`last_name LIKE 'c%'`,优化器则可能利用索引。 ...

    oracle和db2的区别

    ### Oracle与DB2的主要区别 本文旨在探讨Oracle与DB2这两种主流关系型数据库管理系统(RDBMS)之间的关键差异。Oracle由甲骨文公司开发,而DB2则由IBM推出。两者均广泛应用于企业级环境,提供了强大的数据管理能力。...

    oracle遍历数据库查找字符串

    在Oracle数据库中,查找字符串通常涉及SQL的LIKE或INSTR操作符。LIKE用于模糊匹配,允许使用通配符(%表示零个、一个或多个字符,_表示单个字符)。INSTR函数则用来查找子串在目标字符串中的位置,返回值为0表示未...

    Oracle 获得以百分号结尾的脚本有三种写法

    在Oracle数据库中,查询以特定字符结尾的数据是常见的操作,特别是在处理用户输入或者解析字符串时。本文将详细探讨三种不同的方法来获取以百分号(%)结尾的数据行,这些方法适用于Oracle SQL查询。 首先,我们来...

    Oracle 数据库经典练习

    ### Oracle数据库经典练习知识点解析 #### 一、员工信息综合查询与SQL技巧 在Oracle数据库中,通过一系列经典练习,可以深入理解SQL语言的应用及Oracle数据库的管理。以下是对几个关键练习的详细解析: 1. **...

    MySQL Like模糊查询速度太慢如何解决

    3. **Oracle LIKE优化**: - 在Oracle中,对于`LIKE '%%'`应避免使用,因为这会导致全表扫描。 - 对于`LIKE '%...%'`(不以%结尾),可以利用`REVERSE`函数和反向索引来优化为`LIKE '%'`的形式。 - 对于非`'%%'`...

    oracle拼音首字母查询

    Oracle提供了正则表达式(REGEXP_LIKE)函数,以及通过索引来加速查询。例如,假设我们有一个名为`chinese_words`的表,其中`word`列包含汉字,我们可以创建一个函数索引来加速拼音首字母查询: ```sql CREATE ...

Global site tag (gtag.js) - Google Analytics