TestingDocs.com
    Software Testing website
    • Automation
      • Selenium
      • JBehave Framework
    • Tutorials
      • MySQL Tutorials
      • Testlink
      • Maven
      • Git
    • IDEs
      • IntelliJ IDEA
      • Eclipse
    • Flowcharts
      • Flowgorithm
      • Raptor
    • About

    Oracle

    Oracle SQL LIKE Operator with Examples

    Overview

    The LIKE operator is used to filter data based on patterns. The operator is used in the WHERE clause. In the example section, we will look at various examples using the LIKE operator.

    LIKE Operator

    SELECT column_names FROM table_name

    WHERE column_name LIKE ‘pattern’ ;

    Wildcard characters in the pattern and the descriptions:

     

    Wildcard Description
    _ _ The underscore wildcard matches any single character or single-digit
    % The % wildcard matches zero, or more characters or numbers
    [ ] The [] wildcard matches any single character in the specified range
    [^] The wildcard matches any single character except the specified range

     

    Example

    Let’s look at some examples to show the use of the LIKE operator.

    For example, we want to know the employee no, name, and salary for employees whose names start with the letter M.

    Example table data:

    SQL> SELECT empno, ename, sal FROM employee;
    
     EMPNO ENAME SAL
    ---------- -------------------- ----------
     7369 SMITH 800
     7499 ALLEN 1600
     7521 WARD 1250
     7566 JONES 2975
     7654 MARTIN 1250

     

    We can use the % wildcard in this example. The ‘M%’ matches names that start with M and any characters after the letter M.

    SQL> SELECT empno, ename, sal FROM employee
    WHERE ename LIKE ‘M%’;

    # List employee names ending with the letter N.

    Now we use the wildcard % at the beginning and the letter N as the end letters.

    SQL> SELECT empno, ename, sal FROM employee
    2         WHERE ename LIKE ‘%N’;

    EMPNO     ENAME                   SAL
    ———-     ——————–   ———-
    7499           ALLEN                     1600
    7654           MARTIN                  1250

     

    List employee names with ART in the name.

    SQL> SELECT empno, ename, sal FROM employee
    2         WHERE ename LIKE ‘%ART%’;

    Now, we want to use the string ‘%art%’ in the LIKE operator. What change do we need to make the SQL query work?

    —

    Oracle Database Tutorials on this website:

    https://www.testingdocs.com/oracle-database-tutorials-for-beginners/

    More information about Oracle Database:

    https://www.oracle.com/database/

    Related Posts

    Download SQL Developer

    Oracle /

    Download Oracle SQL Developer

    Oracle Databse 19c Download

    Oracle /

    Install Oracle 19c Database On Windows 10

    ORA-01109 Database Not Open

    Oracle /

    Fix ORA-01109: database not open Error

    Oracle SQL Update Statement

    Oracle /

    Oracle SQL Update Statement

    Oracle SQL Insert Statement

    Oracle /

    Oracle SQL INSERT Statement

    ‹ Types of SQL commands› Oracle SQL Data Types

    Recent Posts

    • ChatGPT Subscription Plans
    • Stellar Converter for Database
    • Stellar Log Analyzer for MySQL
    • Stellar Repair for MySQL
    • ChatGPT Capabilities
    • How to secure your SQL Database: Tips and Tricks
    • ChatGPT4 Conversational AI Features
    • Shaping the Future of Development: Exploring Key Trends in Software Engineering
    • Improving Java Performance with Multithreading
    • Open-source Vector Databases

    Back to Top

    Links

    • Contact
    • Privacy Policy
    • Cookie Policy

    www.TestingDocs.com