Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Need to get specific value from string

Muzokh FrazerFeb 21 2024

Hello All

I appreciate if someone help me out. I have the below sample.

Query:

with
sample_data (des, sq) as (
select 'xxx-Region B-Symmetrical 500000 Mbps-2TB Capped Internet-UBB $2.5/GB' , 1
from dual union all
select 'yyy 100 Mbps-1TB Capped Internet-UBB $2/GB-CRTC 3001.1736' , 2
from dual union all
select '2.5/GBTetra byte -Region Z-Symmetrical 100 Mbps-2TB Capped Internet-UBB $', 3
from dual union all
select ' sasasa A-Symmetrical 100 Mbps-1.5TB Capped Internet-UBB $2 /GB- 3001.16' , 4
from dual union all
select '2 Unlimited sasasa A-Symmetrical 100 Mbps-1.5TB Capped Internet-UBB - 3001.16' , 5
from dual union all
select 'Unilimited sasasa A-Symmetrical 100 Mbps-1.5TB Capped Internet-UBB $2/ 3001.16' , 6
from dual union all
Select 'sasasa A-Symmetrical 100 Mbps-1.5TB Capped Internet-UBB $2/ 3001.16 Unlimited' , 7
from dual union all
select ' Peak- 20Mbps-1250GB Capped Internet-UBB $2.5', 8
from dual
)
select sq, nvl(regexp_substr(des, '(\d+(\.\d+)?) ?/GB', 1, 1, 'i', 1), 'N/A') as gb
from sample_data
order by sq;

Query Result

SQ GB

1 2.5
2 2
3 2.5
4 2
5 N/A
6 N/A
7 N/A
8 N/A

Desired Result

1 2.5
2 2
3 2.5
4 2
5 0
6 0
7 0
8 N/A

Rules

if Des contains /GB then show the numeric value before /GB, it is already showing from 1 to 4 rows above, if the Des contains Unlimited then show the value =0 and if there is no value like /GB and Unlimited then show N/A. Thanks

This post has been answered by Paulzip on Feb 21 2024
Jump to Answer
Comments
Post Details
Added on Feb 21 2024
2 comments
108 views