Oracle Live SQL - Script: REGEXP

Statement 1

REM Extracting letter and number sequences from a string

Extracting letter and number sequences from a string

Statement 2

with strings as ( 
  select 'ABC123' str from dual union all 
  select 'A1B2C3' str from dual union all 
  select '123ABC' str from dual union all 
  select '1A2B3C' str from dual 
) 
  select regexp_substr(str, '[0-9]'), /* Returns the first number */  
         regexp_substr(str, '[0-9].*'), /* Returns the first number and the rest of the string */  
         regexp_substr(str, '[A-Z][0-9]') /* Returns the first letter with a following number */  
  from   strings

 
REGEXP_SUBSTR(STR,'[0-9]') REGEXP_SUBSTR(STR,'[0-9].*') REGEXP_SUBSTR(STR,'[A-Z][0-9]')/*RETURNSTHEFIRSTLETTERWITHAFOLLOWINGNUMBER*/
1 123 C1
1 1B2C3 A1
1 123ABC  - 
1 1A2B3C A2

4 rows selected.

REGEXP_SUBSTR(STR,'[0-9]')	REGEXP_SUBSTR(STR,'[0-9].*')	REGEXP_SUBSTR(STR,'[A-Z][0-9]')/RETURNSTHEFIRSTLETTERWITHAFOLLOWINGNUMBER/
1	123	C1
1	1B2C3	A1
1	123ABC	-
1	1A2B3C	A2

Statement 3

REM Extracting passenger names from flight information using REGEXP_SUBSTR

Extracting passenger names from flight information using REGEXP_SUBSTR

Statement 4

REM Format of string is Route (departure & desitination airports) / departure date (ddmmyyyy) / Passenger name

Format of string is Route (departure & desitination airports) / departure date (ddmmyyyy) / Passenger name

Statement 5

with strings as ( 
  select 'LHRJFK/010315/SAXONMR' str from dual union all 
  select 'CDGLAX/050515/SMITHMRS' str from dual union all 
  select 'LAXCDG/220515/SMITHMRS' str from dual union all 
  select 'SFOJFK/010615/JONESMISS' str from dual 
) 
  select regexp_substr(str, '[A-Z]{6}'), /* Returns the first string of 6 characters */  
         regexp_substr(str, '[0-9]+'), /* Returns the first matching numbers */  
         regexp_substr(str, '[A-Z].*$'), /* Returns the first letter followed by all other characters */  
         regexp_substr(str, '/[A-Z].*$') /* Returns / followed by a letter then all other characters */  
  from   strings

 
REGEXP_SUBSTR(STR,'[A-Z]{6}') REGEXP_SUBSTR(STR,'[0-9]+') REGEXP_SUBSTR(STR,'[A-Z].*$') REGEXP_SUBSTR(STR,'/[A-Z].*$')/*RETURNS/FOLLOWEDBYALETTERTHENALLOTHERCHARACTERS*/
LHRJFK 010315 LHRJFK/010315/SAXONMR /SAXONMR
CDGLAX 050515 CDGLAX/050515/SMITHMRS /SMITHMRS
LAXCDG 220515 LAXCDG/220515/SMITHMRS /SMITHMRS
SFOJFK 010615 SFOJFK/010615/JONESMISS /JONESMISS

4 rows selected.

REGEXP_SUBSTR(STR,'[A-Z]{6}')	REGEXP_SUBSTR(STR,'[0-9]+')	REGEXP_SUBSTR(STR,'[A-Z].*$')	REGEXP_SUBSTR(STR,'/[A-Z].$')/RETURNS/FOLLOWEDBYALETTERTHENALLOTHERCHARACTERS*/
LHRJFK	010315	LHRJFK/010315/SAXONMR	/SAXONMR
CDGLAX	050515	CDGLAX/050515/SMITHMRS	/SMITHMRS
LAXCDG	220515	LAXCDG/220515/SMITHMRS	/SMITHMRS
SFOJFK	010615	SFOJFK/010615/JONESMISS	/JONESMISS

Statement 6

REM Extracting letter and number sequences from a string

Extracting letter and number sequences from a string

Statement 7

with strings as ( 
  select 'ABC123' str from dual union all 
  select 'A1B2C3' str from dual union all 
  select '123ABC' str from dual union all 
  select '1A2B3C' str from dual 
) 
  select regexp_substr(str, '[0-9]') first_number, /* Returns the first number */  
         regexp_substr(str, '[0-9].*') first_number_then_everything, /* Returns the first number and the rest of the string */  
         regexp_substr(str, '[A-Z][0-9]') first_letter_w_number_after /* Returns the first letter with a following number */  
  from   strings

ORA-00972: identifier is too long

More Details: https://docs.oracle.com/error-help/db/ora-00972

Statement 8

REM Extracting passenger names from flight information using REGEXP_SUBSTR

Extracting passenger names from flight information using REGEXP_SUBSTR

Statement 9

REM Format of string is Route (departure & desitination airports) / departure date (ddmmyyyy) / Passenger name

Format of string is Route (departure & desitination airports) / departure date (ddmmyyyy) / Passenger name

Statement 10

with strings as ( 
  select 'LHRJFK/010315/SAXONMR' str from dual union all 
  select 'CDGLAX/050515/SMITHMRS' str from dual union all 
  select 'LAXCDG/220515/SMITHMRS' str from dual union all 
  select 'SFOJFK/010615/JONESMISS' str from dual 
) 
  select regexp_substr(str, '[A-Z]{6}') first_6_letters, /* Returns the first string of 6 characters */  
         regexp_substr(str, '[0-9]+') first_matching_numbers, /* Returns the first matching numbers */  
         regexp_substr(str, '[A-Z].*$') first_letter_then_all, /* Returns the first letter followed by all other characters */  
         regexp_substr(str, '/[A-Z].*$') first_slash_w_letter_after /* Returns / followed by a letter then all other characters */  
  from   strings

ORA-00923: FROM keyword not found where expected

More Details: https://docs.oracle.com/error-help/db/ora-00923

Statement 11

REM Extracting letter and number sequences from a string

Extracting letter and number sequences from a string

Statement 12

with strings as ( 
  select 'ABC123' str from dual union all 
  select 'A1B2C3' str from dual union all 
  select '123ABC' str from dual union all 
  select '1A2B3C' str from dual 
) 
  select regexp_substr(str, '[0-9]') first_number, /* Returns the first number */  
         regexp_substr(str, '[0-9].*') first_number_followed_by_all, /* Returns the first number and the rest of the string */  
         regexp_substr(str, '[A-Z][0-9]') first_letter_with_num_after /* Returns the first letter with a following number */  
  from   strings

 
FIRST_NUMBER FIRST_NUMBER_FOLLOWED_BY_ALL FIRST_LETTER_WITH_NUM_AFTER
1 123 C1
1 1B2C3 A1
1 123ABC  - 
1 1A2B3C A2

4 rows selected.

FIRST_NUMBER	FIRST_NUMBER_FOLLOWED_BY_ALL	FIRST_LETTER_WITH_NUM_AFTER
1	123	C1
1	1B2C3	A1
1	123ABC	-
1	1A2B3C	A2

Statement 13

REM Extracting passenger names from flight information using REGEXP_SUBSTR

Extracting passenger names from flight information using REGEXP_SUBSTR

Statement 14

REM Format of string is Route (departure & desitination airports) / departure date (ddmmyyyy) / Passenger name

Format of string is Route (departure & desitination airports) / departure date (ddmmyyyy) / Passenger name

Statement 15

with strings as ( 
  select 'LHRJFK/010315/SAXONMR' str from dual union all 
  select 'CDGLAX/050515/SMITHMRS' str from dual union all 
  select 'LAXCDG/220515/SMITHMRS' str from dual union all 
  select 'SFOJFK/010615/JONESMISS' str from dual 
) 
  select regexp_substr(str, '[A-Z]{6}') first_6_letters, /* Returns the first string of 6 characters */  
         regexp_substr(str, '[0-9]+') first_matching_numbers, /* Returns the first matching numbers */  
         regexp_substr(str, '[A-Z].*$') first_letter_then_all, /* Returns the first letter followed by all other characters */  
         regexp_substr(str, '/[A-Z].*$') first_slash_w_letter_after /* Returns / followed by a letter then all other characters */  
  from   strings

ORA-00972: identifier is too long

More Details: https://docs.oracle.com/error-help/db/ora-00972

Statement 16

REM Extracting letter and number sequences from a string

Extracting letter and number sequences from a string

Statement 17

with strings as ( 
  select 'ABC123' str from dual union all 
  select 'A1B2C3' str from dual union all 
  select '123ABC' str from dual union all 
  select '1A2B3C' str from dual 
) 
  select regexp_substr(str, '[0-9]') first_number, /* Returns the first number */  
         regexp_substr(str, '[0-9].*') first_number_followed_by_all, /* Returns the first number and the rest of the string */  
         regexp_substr(str, '[A-Z][0-9]') first_letter_with_num_after /* Returns the first letter with a following number */  
  from   strings

 
FIRST_NUMBER FIRST_NUMBER_FOLLOWED_BY_ALL FIRST_LETTER_WITH_NUM_AFTER
1 123 C1
1 1B2C3 A1
1 123ABC  - 
1 1A2B3C A2

4 rows selected.

FIRST_NUMBER	FIRST_NUMBER_FOLLOWED_BY_ALL	FIRST_LETTER_WITH_NUM_AFTER
1	123	C1
1	1B2C3	A1
1	123ABC	-
1	1A2B3C	A2

Statement 18

REM Extracting passenger names from flight information using REGEXP_SUBSTR

Extracting passenger names from flight information using REGEXP_SUBSTR

Statement 19

REM Format of string is Route (departure & desitination airports) / departure date (ddmmyyyy) / Passenger name

Format of string is Route (departure & desitination airports) / departure date (ddmmyyyy) / Passenger name

Statement 20

with strings as ( 
  select 'LHRJFK/010315/SAXONMR' str from dual union all 
  select 'CDGLAX/050515/SMITHMRS' str from dual union all 
  select 'LAXCDG/220515/SMITHMRS' str from dual union all 
  select 'SFOJFK/010615/JONESMISS' str from dual 
) 
  select regexp_substr(str, '[A-Z]{6}') first_6_letters, /* Returns the first string of 6 characters */  
         regexp_substr(str, '[0-9]+') first_matching_numbers, /* Returns the first matching numbers */  
         regexp_substr(str, '[A-Z].*$') first_letter_then_all, /* Returns the first letter followed by all other characters */  
         regexp_substr(str, '/[A-Z].*$') forward_slash_w_letter_after /* Returns / followed by a letter then all other characters */  
  from   strings

 
FIRST_6_LETTERS FIRST_MATCHING_NUMBERS FIRST_LETTER_THEN_ALL FORWARD_SLASH_W_LETTER_AFTER
LHRJFK 010315 LHRJFK/010315/SAXONMR /SAXONMR
CDGLAX 050515 CDGLAX/050515/SMITHMRS /SMITHMRS
LAXCDG 220515 LAXCDG/220515/SMITHMRS /SMITHMRS
SFOJFK 010615 SFOJFK/010615/JONESMISS /JONESMISS

4 rows selected.

REGEXP_SUBSTR examples

Additional Information