Use LIKE Conditions in Oracle

Home > Oracle

Q

How To Use LIKE Conditions in Oracle?

✍: FYIcenter.com

A

LIKE condition is also called pattern patch. There 3 main rules on using LIKE condition:

'_' is used in the pattern to match any one character.
'%' is used in the pattern to match any zero or more characters.
ESCAPE clause is used to provide the escape character in the pattern.

The following script provides you some good pattern matching examples:

SELECT CASE WHEN 'FYICenter.com' LIKE '%Center%' 
  THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE   

SELECT CASE WHEN 'FYICenter.com' LIKE '%CENTER%' 
  THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
  -- Case sensitive by default
FALSE

SELECT CASE WHEN 'FYICenter.com' LIKE '%Center_com' 
  THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE   

SELECT CASE WHEN '100% correct' LIKE '100\% %' ESCAPE '\' 
  THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE

⇒ Use of Regular Expression in Oracle

⇐ Use IN Conditions in Oracle

⇑ Understanding SQL Language Basics for Oracle

⇑⇑ Oracle Database Tutorials

2020-03-15, 3206🔥, 0💬

Create a New Table with SELECT Statements in Oracle
How To Create a New Table by Selecting Rows from Another Table in Oracle? Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the CREATE TABLE...AS SELECT statement to do this. Here is an example script: &... 2020-02-29, 3663🔥, 0💬

Concatenate Two Text Values in Oracle
How To Concatenate Two Text Values in Oracle? There are two ways to concatenate two text values together: CONCAT() function. '||' operation. Here is some examples on how to use them: SELECT 'FYI' || 'Center' || '.com' FROM DUAL; FYICenter.com SELECT CONCAT('FYICenter','.com') FROM DUAL; FYICenter.co... 2019-12-02, 3351🔥, 0💬

Use LIKE Conditions in Oracle
How To Use LIKE Conditions in Oracle? LIKE condition is also called pattern patch. There 3 main rules on using LIKE condition: '_' is used in the pattern to match any one character. '%' is used in the pattern to match any zero or more characters. ESCAPE clause is used to provide the escape character... 2020-03-15, 3207🔥, 0💬

Create a New Table in Oracle
How To Create a New Table in Oracle? If you want to create a new table in your own schema, you can log into the server with your account, and use the CREATE TABLE statement. The following script shows you how to create a table: >.\bin\sqlplus /nolog SQL> connect HR/fyicenter Connected.... 2020-02-29, 3109🔥, 0💬

Understanding SQL DDL Statements for Oracle
Where to find answers to frequently asked questions on SQL DDL Statements for Oracle? I want to start creating tables. Here is a list of frequently asked questions and their answers compiled by FYIcenter.com DBA team on SQL DDL Statements for Oracle. Clear answers are provided with tutorial exercise... 2020-02-29, 2973🔥, 0💬

Use of Regular Expression in Oracle
How To Use Regular Expression in Pattern Match Conditions in Oracle? If you have a pattern that is too complex for LIKE to handle, you can use the regular expression pattern patch function: REGEXP_LIKE(). The following script provides you some good examples: SELECT CASE WHEN REGEXP_LIKE ('FYICenter.... 2020-03-15, 2946🔥, 0💬

What Are DDL Statements in Oracle
What Are DDL Statements in Oracle? DDL (Data Definition Language) statements are statements to create and manage data objects in the database. The are 3 primary DDL statements: CREATE - Creating a new database object. ALTER - Altering the definition of an existing data object. DROP - Dropping an exi... 2020-02-29, 2888🔥, 0💬

Use NULL as Conditions in Oracle
How To Use NULL as Conditions in Oracle? If you want to compare values against NULL as conditions, you should use the "IS NULL" or "IS NOT NULL" operator. Do not use "=" or "<>" against NULL. The sample script below shows you some good examples: SELECT 'A' IS NULL FROM DUAL; -- Error: ... 2019-12-02, 2874🔥, 0💬

Use IN Conditions in Oracle
How To Use IN Conditions in Oracle? An IN condition is single value again a list of values. It returns TRUE, if the specified value is in the list. Otherwise, it returns FALSE. Some examples are given in the script below: SELECT CASE WHEN 3 IN (1,2,3,5) THEN 'TRUE' ELSE 'FALSE' END FROM DUAL; TRUE S... 2020-03-15, 2809🔥, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.