Skip to Main Content
  • Questions
  • Want to replace a particular string with a null value

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ankur.

Asked: February 13, 2020 - 8:13 am UTC

Answered by: Chris Saxon - Last updated: February 14, 2020 - 11:30 am UTC

Category: PL/SQL - Version: 10

Viewed 100+ times

You Asked

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.

and we said...

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> 

and you rated our response

  (1 rating)

Reviews

It should be > 0

February 14, 2020 - 7:52 am UTC

Reviewer: A reader

> 1 will skip rows beginning with end date
Chris Saxon

Followup  

February 14, 2020 - 11:30 am UTC

True.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.