Oracle REGEXP_LIKE Examples

Regular expressions are patterns used to match character combinations in strings. Oracle 10g introduced support for regular expressions using different functions. This post focuses on the Oracle REGEXP_LIKE function, and explains how to use it.

Description

the Oracle REGEXP_LIKE is used to perform a regular expression matching (rather than a simple pattern matching performed by LIKE).

syntax

1	REGEXP_LIKE ( string expression, pattern [, matching parameter ] )

• string expression – the string expression.
• pattern – the regular expression matching pattern
• match parameter – lets you to change the default matching behaviour of the Oracle REGEXP_LIKE function (for example, change the search from case sensitive to case insensitive).

Basic Oracle REGEXP_LIKE Examples

We’ll start by creating a table called Names, based on its values, the following Oracle REGEXP_LIKE examples will perform different regular expression searches.

1 2 3 4 5

CREATE TABLE names AS SELECT last_name AS NAME FROM hr.employees ORDER BY salary ;

The following Oracle REGEXP_LIKE example would retrieve all of the names that contain the letter ‘z’. This Oracle SELECT statement actually puts no lower or upper limit on the number of letters before or after the letter ‘z’ (any number of characters is allowed), but requires the word to contain the letter ‘z’.

01 02 03 04 05 06 07 08 09 10

SELECT * FROM names WHERE regexp_like (name , 'z') ; NAME ------------------------- Lorentz Gietz Ozer

The next Oracle REGEXP_LIKE example would retrieve all of the names that contain the letter-sequence ‘be’. Again, this Oracle SELECT statement actually puts no lower or upper limit on the number of letters before or after the letter-sequence ‘be’ (any number of characters is allowed), but requires the word to contain the letter-sequence ‘be’.

1 2 3 4 5 6 7 8

SELECT * FROM names WHERE regexp_like (name , 'be') ; NAME --------------------------- Abel Greenberg

using the pipe (|) operator

The Pipe operator (|) is used to specify alternative matches. In the next Oracle REGEXP_LIKE example we would use the pipe operator (|) in order to retrieve all of the names that contain the letter-sequence ‘be’ or ‘ae’. This Oracle SELECT statement actually puts no lower or upper limit on the number of letters before or after the letter-sequence ‘be’ or ‘ae'(any number of characters is allowed), but requires the word to contain these sequences.

01 02 03 04 05 06 07 08 09 10

SELECT * FROM names WHERE regexp_like (name , 'be|ae') ; NAME ------------------------- Baer Abel Raphaely Greenberg

By specifying the letter ‘c’ (as the third argument of the REGEXP_LIKE function) we can make a case sensitive search (the default in Oracle).

01 02 03 04 05 06 07 08 09 10

SELECT * FROM names WHERE regexp_like (name , 'be|ae' , 'c' ) ; NAME ------------------------- Baer Abel Raphaely Greenberg

And by specifying the letter ‘i’ (as the third argument of the REGEXP_LIKE function) we can make a case insensitive search.

01 02 03 04 05 06 07 08 09 10 11 12

SELECT * FROM names WHERE regexp_like (name , 'be|ae' , 'i' ) ; NAME ------------------------- Bell Bernstein Baer Abel Raphaely Greenberg

Using the Caret(^) operator

We can use the caret (^) operator to indicate a beginning-of-line character, in this REGEXP_LIKE example we would retrieve all names that start with the letter-sequence ‘be’ or ‘ba’ (case insensitive search)

01 02 03 04 05 06 07 08 09 10 11 12

SELECT * FROM names WHERE regexp_like (name , '^be|^ba' , 'i' ) ; NAME ------------------------- Baida Bell Banda Bates Bernstein Baer

Using the Dollar ($) operator

We can use the dollar ($) operator to indicate an end-of-line character, in this REGEXP_LIKE example we would retrieve all names that end with the letter-sequence ‘es’ or ‘er’ (case insensitive search).

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16

SELECT * FROM names WHERE regexp_like (name , 'es$|er$' , 'i' ) ; NAME ------------------------- Philtanker Colmenares Jones Gates Davies Nayer Stiles Dellinger Bates Baer

Using Square Brackets

We can use the Square Brackets to specify a matching list that should match any one of the expressions represented in it. The next Oracle REGEXP_LIKE example would retrieve all names that contain the letters ‘j’ or ‘z’.

01 02 03 04 05 06 07 08 09 10 11

SELECT * FROM names WHERE regexp_like (name , '[jz]') ; NAME ------------------------- Rajs Lorentz Gietz Ozer Errazuriz

This REGEXP_LIKE example would retrieve all names that contain the letters ‘b’ or ‘z’ or ‘E’ (case sensitive search)

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17

SELECT *  FROM names  WHERE regexp_like (name , '[bzE]') ; NAME ------------------------- Tobias Cabrio Everett Lorentz Pataballa Ernst Cambrault Gietz McEwen Cambrault

Next, we’ll modify our last query and make it a case insensitive search :

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17

SELECT * FROM names WHERE regexp_like (name , '[bzE]' , 'i') ; NAME ------------------------- Philtanker Zachary Markle Gee Perkins Colmenares Patel OConnell Mikkilineni Tobias Seo

This Oracle REGEXP_LIKE example would retrieve all the names that contain the letters ‘a’, ‘b’, or ‘c’ :

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16

SELECT * FROM names WHERE regexp_like (name , '[abc]') ; NAME ------------------------- Philtanker Markle Landry Colmenares Patel Vargas Sullivan Marlow Grant Matos

And instead of specifying the letters ‘a’, ‘b’ and ‘c’ separately, we can specify a range :

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16

SELECT * FROM names WHERE regexp_like (name , '[a-c]') ; NAME ------------------------- Philtanker Markle Landry Colmenares Patel Vargas Sullivan Marlow Grant Matos

The next Oracle REGEP_LIKE example would retrieve all names that contain a letter in the range of ‘d’ and ‘g’, followed by the letter ‘a’.

01 02 03 04 05 06 07 08 09 10

SELECT * FROM names WHERE regexp_like (name , '[d-g]a') ; NAME ------------------------- Vargas Baida Fleaur Banda

Using the Period (.) Operator

The period (.) operator matches any character except NULL, the next Oracle REGEXP_LIKE example would retrieve all names that contain a letter in the range of ‘b’ and ‘g’, followed by any character, followed by the letter ‘a’.

01 02 03 04 05 06 07 08 09 10 11 12 13 14

SELECT * FROM names WHERE regexp_like (name , '[b-g].[a]') ; NAME ------------------------- Colmenares Tobias McCain Sarchand Sewall Cambrault Sciarra Cambrault

We can use the Period Operator to represent more than one character, the next Oracle REGEXP_LIKE example would retrieve all names that contain a letter in the range of ‘b’ and ‘g’, followed by any two characters, followed by the letter ‘a’.

1 2 3 4 5 6 7 8

SELECT * FROM names WHERE regexp_like (name , '[b-g]..[a]') ; NAME ------------------------- De Haan Kochhar

Using the curly brackets

The curly brackets are used to specify an exact number of occurrences, for example display all names that contain double ‘o’ letters.

1 2 3 4 5 6 7 8

SELECT * FROM names WHERE regexp_like (name , '[o]{2}') ; NAME ------------------------- Khoo Bloom

Pass multiple values to a parameters REGEXP_SUBSTR

Input Values Examples : P_ORDER_NUMBER = 5502158, 70045432 , 70045421

SELECT   *
  FROM   oe_order_headers_all
 WHERE   1 = 1
         AND ( (ORDER_NUMBER IN (    SELECT   REGEXP_SUBSTR (:P_ORDER_NUMBER
                                                      , '[^,]+'
                                                      , 1
                                                      , LEVEL)
                                  FROM   DUAL
                            CONNECT BY   REGEXP_SUBSTR (:P_ORDER_NUMBER
                                                      , '[^,]+'
                                                      , 1
                                                      , LEVEL) IS NOT NULL))
              OR (ORDER_NUMBER IS NULL AND :P_ORDER_NUMBER IS NULL)
              OR:P_ORDER_NUMBER IS NULL);

remove concurrent program from its request group & delete concurrent program definition and executable from back-end

-------------------------------------------------------------------------------
--  remove concurrent program from its request group  and then
--  delete concurrent program definition and executable from back-end
-------------------------------------------------------------------------------
-- syntax:
--     delete_program    (program_short_name, application_short_name)
--     delete_executable (program_short_name, application_short_name)
-------------------------------------------------------------------------------

DECLARE
   lv_prog_short_name      VARCHAR2 (240);
   lv_appl_short_name      VARCHAR2 (240);
   lv_request_group_appl   VARCHAR2 (240);
   lv_request_group_name   VARCHAR2 (240);

   CURSOR c1
   IS
      SELECT   cp.concurrent_program_name concurrent_program_short_name
             , cpt.user_concurrent_program_name
             , cp_appl.application_short_name conc_prgm_appl_short_name
             , cp_appl.application_name conc_prgm_appl_name
             , rg.request_group_name
             , cp_req_grp.application_short_name req_group_appl_short_name
             , cp_req_grp.application_name req_group_appl_name
             , DECODE (rgu.request_unit_type,
                       'P', 'Program',
                       'S', 'Set',
                       rgu.request_unit_type)
                  "Unit Type"
        FROM   fnd_request_groups rg
             , fnd_request_group_units rgu
             , fnd_concurrent_programs cp
             , fnd_concurrent_programs_tl cpt
             , fnd_application_vl cp_appl
             , fnd_application_vl cp_req_grp
       WHERE       rg.request_group_id = rgu.request_group_id
               AND rgu.request_unit_id = cp.concurrent_program_id
               AND cp.concurrent_program_id = cpt.concurrent_program_id
               AND cpt.user_concurrent_program_name =
                     'USPS NCMS Reverse Lockbox Import'
               AND cp_req_grp.application_id = rg.application_id
               AND cp_appl.application_id = cp.application_id;
BEGIN
   -- set the variables first

   FOR c1_rec IN c1
   LOOP
      lv_prog_short_name := c1_rec.concurrent_program_short_name;
      -- concurrent program short name
      lv_appl_short_name := c1_rec.conc_prgm_appl_short_name;

      -- application short name

      BEGIN
         fnd_program.remove_from_group (c1_rec.concurrent_program_short_name -- program_short_name
                                      , c1_rec.conc_prgm_appl_name -- application
                                      , c1_rec.request_group_name -- report group name
                                      , c1_rec.req_group_appl_name -- report group application
                                                                  );

         DBMS_OUTPUT.put_line(   'Concurrent Program '
                              || c1_rec.concurrent_program_short_name
                              || ' removed successfully from Request Group '
                              || c1_rec.request_group_name);
         COMMIT;
      END;
   END LOOP;

   -- see if the program exists. if found, delete the program
   IF fnd_program.program_exists (lv_prog_short_name, lv_appl_short_name)
      AND fnd_program.executable_exists (lv_prog_short_name
                                       , lv_appl_short_name)
   THEN
      fnd_program.delete_program (lv_prog_short_name, lv_appl_short_name);
      fnd_program.delete_executable (lv_prog_short_name, lv_appl_short_name);

      COMMIT;

      DBMS_OUTPUT.put_line (lv_prog_short_name || ' deleted successfully');
   -- if the program does not exist in the system
   ELSE
      DBMS_OUTPUT.put_line (lv_prog_short_name || ' not found');
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error: ' || SQLERRM);
END;

How to Copy File Names in Windows Explorer

Display File Extensions

1. Click "Organize" in the Windows Explorer toolbar and select "Folder and Search Options."

2. Click the "View" tab.

3. Uncheck "Hide Extensions for Known File Types." You will now be able to copy the entire file name, including the extension.

4. Click "OK."

Copy File Name

1. Open Windows Explorer and click the file to select it.

2. Press "F2" on your keyboard to select the name. This enables you to rename the file or copy the name. Alternatively, right-click the file and select "Rename."

3. Press "Ctrl-A" if you also need to copy the file extension. This requires the previous unchecking of "Hide Extensions for Known File Types."

4. Press "Ctrl-C" to copy the file name.

5. Press "Enter" or click another location to exit the renaming mode.

Copy Entire List of Files

1. Hold the "Shift" key, right-click the folder containing a list of files and select "Open Command Window Here."

2. Type "dir /b > filenames.txt" (without quotation marks) in the Command Prompt window. Press "Enter."

3. Double-click the "filenames.txt" file from the previously selected folder to see a list of file names in that folder.

4. Press "Ctrl-A" and then "Ctrl-C" to copy the list of file names to your clipboard.