How To Use Regular Expression in Pattern Match Conditions

Q

How To Use Regular Expression in Pattern Match Conditions? - Oracle DBA FAQ - Understanding SQL Basics

✍: FYIcenter.com

A

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.com', '.*fyi.*',
  'i') THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE

SELECT CASE WHEN REGEXP_LIKE ('FYICenter.com', '.*com$',
  'i') THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE

SELECT CASE WHEN REGEXP_LIKE ('FYICenter.com', '^F.*','i')
  THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE

2007-04-22, 5026👍, 0💬

💬 2016-07-06 Julie: Good to know. Thanks.

💬 2010-10-11 Coleen: I have an Oracle table with a column (seq_num) that needs to populated with sequential nbrs based on a group. For example, I hav...

How To Calculate Date and Time Differences
How To Calculate Date and Time Differences? - Oracle DBA FAQ - Understanding SQL Basics If you want to know how many years, months, days and seconds are there between two dates or times, you can use the date and time interval expressions: YEAR ... TO MONTH and DAY ... TO SECOND. The tutorial exercis... 2007-04-23, 5825👍, 0💬

How To Concatenate Two Text Values
How To Concatenate Two Text Values? - Oracle DBA FAQ - Understanding SQL Basics 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('FYICente... 2007-04-23, 5356👍, 0💬

What Are DDL Statements
What Are DDL Statements? - Oracle DBA FAQ - Understanding SQL DDL Statements 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 ex... 2007-04-22, 5077👍, 0💬

How To Use IN Conditions
How To Use IN Conditions? - Oracle DBA FAQ - Understanding SQL Basics 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' ... 2007-04-23, 5072👍, 0💬

How To Use Regular Expression in Pattern Match Conditions
How To Use Regular Expression in Pattern Match Conditions? - Oracle DBA FAQ - Understanding SQL Basics 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 ... 2007-04-22, 5027👍, 0💬

How To Add a New Column to an Existing Table
How To Add a New Column to an Existing Table? - Oracle DBA FAQ - Understanding SQL DDL Statements If you have an existing table with existing data rows, and want to add a new column to that table, you can use the ALTER TABLE ... ADD statement to do this. Here is an example script: SQL> connect HR/fy... 2007-04-22, 4976👍, 0💬

How To Create a New Table by Selecting Rows from Another Table
How To Create a New Table by Selecting Rows from Another Table? - Oracle DBA FAQ - Understanding SQL DDL Statements 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 t... 2007-04-22, 4975👍, 0💬

How To Delete a Column in an Existing Table
How To Delete a Column in an Existing Table? - Oracle DBA FAQ - Understanding SQL DDL Statements If you have an existing column in a table and you need that column any more, you can delete it with ALTER TABLE ... DROP COLUMN statement. Here is an example SQL script: SQL> CREATE TABLE emp_dept_90 2 A... 2007-04-22, 4819👍, 0💬

How To Create a New Table
How To Create a New Table? - Oracle DBA FAQ - Understanding SQL DDL Statements 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> con... 2007-04-22, 4780👍, 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.