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

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Ankur.

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

Last updated: April 27, 2020 - 4:08 pm UTC

Version: 10

Viewed 1000+ 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 Chris 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> 

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

It should be > 0

A reader, February 14, 2020 - 7:52 am UTC

> 1 will skip rows beginning with end date
Chris Saxon
February 14, 2020 - 11:30 am UTC

True.

Please help me to answer on this below scenario/answer

Ankur Pal, April 22, 2020 - 4:53 am UTC

Introduction:
A database that supports a file downloading service consists of three tables: member, plan, and download. The member table has a list of the members and has a plan_id that specifies which plan the member is on. Each plan has a different download limit, and this download limit (in MB) is stored in the plan table along with the plan_id. The download table has a list of the individual downloads that each member has performed, as well as the date of the download and the number of MB that the download used.

The data in each of the tables is as follows:
--Table-->
member
member_id first_name last_name plan_id
1 Dave C 1
2 Tom K 2
3 John D 2

--Table-->
plan
plan_id download_limit_mb
1 10
2 20
3 30

--Table-->
download
member_id download_date download_amount_mb
1 1/1/2011 1
1 1/2/2011 2
1 1/2/2011 3
2 1/3/2011 2
2 1/3/2011 1
3 1/3/2011 2
3 1/4/2011 3

Question -->
1. Provide the SQL to return the first name and last name of the member with the highest download amount, as well as the first and last day that they performed downloads on. The results of the query should be:

first_name last_name first_date last_date
Dave C 1/1/2011 1/2/2011

Question -->
2. Provide the SQL to return the first name, last name, and the amount remaining before they hit their download limit (you need to find the download limit from the plan table and subtract any amounts from the download table). The results of the query should be:

first_name last_name download_remaining
Dave C 4
Tom K 17
John D 15

Chris Saxon
April 22, 2020 - 9:58 am UTC

I'm not sure how this relates to the original question?

Sorry to not mention that. This is a new question.

Ankur Pal, April 24, 2020 - 4:28 pm UTC

This is a different question. I am searching for the answer. Thanks for your understanding.
Chris Saxon
April 27, 2020 - 4:08 pm UTC

Please submit it as a new question. If we're not accepting new questions, register yourself with the "Notify Me" button to be alerted when we are.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.