REM Extracting letter and number sequences from a string
Extracting letter and number sequences from a string
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 |
|---|
REM Extracting passenger names from flight information using REGEXP_SUBSTR
Extracting passenger names from flight information using REGEXP_SUBSTR
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
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 |
|---|
REM Extracting letter and number sequences from a string
Extracting letter and number sequences from a string
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 longMore Details: https://docs.oracle.com/error-help/db/ora-00972
REM Extracting passenger names from flight information using REGEXP_SUBSTR
Extracting passenger names from flight information using REGEXP_SUBSTR
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
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 expectedMore Details: https://docs.oracle.com/error-help/db/ora-00923
REM Extracting letter and number sequences from a string
Extracting letter and number sequences from a string
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 |
|---|
REM Extracting passenger names from flight information using REGEXP_SUBSTR
Extracting passenger names from flight information using REGEXP_SUBSTR
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
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 longMore Details: https://docs.oracle.com/error-help/db/ora-00972
REM Extracting letter and number sequences from a string
Extracting letter and number sequences from a string
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 |
|---|
REM Extracting passenger names from flight information using REGEXP_SUBSTR
Extracting passenger names from flight information using REGEXP_SUBSTR
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
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 |
|---|