We have 2 types of record_data format in table speedwing table
1st type -->
..NTP ID MT20190125 - NTP PRODUCT META BASIC FIX 2019 - TRAVEL START DATE 24/01/2019 - TRAVEL END DATE 31/12/20'
..NTP ID MT20190125 - NTP PRODUCT META BASIC FIX 2019 - TRAVEL START DATE 24/01/2019 - TRAVEL END DATE 31/12/20'
2nd type -->
..NTP ID FMTAR6R1 - NTP PRODUCT SPECIAL OFFER - SAVE 20 PERCENT - TRAVEL START DATE 11/02/2020 - TRAVEL END DAT'
..NTP ID EQ2VX8N1 - NTP PRODUCT SPECIAL PROMO SPECIAL 15PCT ALL SYSTEM - TRAVEL START DATE 06/01/2020 - TRAVEL'
..NTP ID EQ2VX8N1 - NTP PRODUCT SPECIAL PROMO SPECIAL 15PCT ALL SYSTEM - TRAVEL START DATE 06/01/2020 - TRAVEL'
Current query -->
select
TRIM (
REPLACE (
SUBSTR (
record_data,
INSTR(record_data,'TRAVEL END DATE',1) + 15),
''''))
travel_end_date
from speedwing
WHERE record_data LIKE '%NTP ID%NTP PRODUCT%'
AND record_data LIKE '%- TRAVEL START DATE%'
AND record_data LIKE '%..NTP%';
In this query the 2nd type of record data given travel and date as a string value. Below is the example:
..NTP ID EQ2VX8N1 - NTP PRODUCT SPECIAL PROMO SPECIAL 15PCT ALL SYSTEM - TRAVEL START DATE 06/01/2020 - TRAVEL
..NTP ID GP7N22F1 - NTP PRODUCT PROMO SPECIALE 20 BA TG FL CO PY - TRAVEL START DATE 21/01/2020 - TRAVEL END DA
But the requirement is this should be replaced as NULL value in case if it is return string.
May you please help me to find out this query.
Thanks in advanced.
So you want to return NULL if TRAVEL END DATE is missing from the text?
If so, use a case expression to check that the value is present before processing it:
create table speedwing (
record_data varchar2(200)
);
insert into speedwing values ( '..NTP ID MT20190125 - NTP PRODUCT META BASIC FIX 2019 - TRAVEL START DATE 24/01/2019 - TRAVEL END DATE 31/12/20');
insert into speedwing values ( '..NTP ID MT20190125 - NTP PRODUCT META BASIC FIX 2019 - TRAVEL START DATE 24/01/2019 - TRAVEL END DATE 31/12/20');
insert into speedwing values ( '..NTP ID FMTAR6R1 - NTP PRODUCT SPECIAL OFFER - SAVE 20 PERCENT - TRAVEL START DATE 11/02/2020 - TRAVEL END DAT');
insert into speedwing values ( '..NTP ID EQ2VX8N1 - NTP PRODUCT SPECIAL PROMO SPECIAL 15PCT ALL SYSTEM - TRAVEL START DATE 06/01/2020 - TRAVEL');
insert into speedwing values ( '..NTP ID EQ2VX8N1 - NTP PRODUCT SPECIAL PROMO SPECIAL 15PCT ALL SYSTEM - TRAVEL START DATE 06/01/2020 - TRAVEL');
select
case
when INSTR(record_data,'TRAVEL END DATE',1) > 1 then
TRIM (
REPLACE (
SUBSTR (
record_data,
INSTR(record_data,'TRAVEL END DATE',1) + 15
),
''''
)
)
end travel_end_date
from speedwing
WHERE record_data LIKE '%NTP ID%NTP PRODUCT%'
AND record_data LIKE '%- TRAVEL START DATE%'
AND record_data LIKE '%..NTP%';
TRAVEL_END_DATE
31/12/20
31/12/20
<null>
<null>
<null>