Oracle regexp_substr to extract data

Question

I am trying to extract download and upload speed number from the string, not able to achieve it, please help

Input String: My ADSL 14Mbps/2M speed

Expected output:

Download_speed = 14
Upload_speed = 2

My SQL

SELECT regexp_substr('My ADSL 14Mbps/2M speed','[^\/]+') donwload_speed
      ,regexp_substr('My ADSL 14Mbps/2M speed','[^\/]+$') upload_speed
  FROM dual

output is coming as from above SQL

download_speed = My Fiber 14Mbps
upload_speed = 2M speed

Tip: Before posting, do a select distinct on your data and make sure the sample data you post contains all possible examples of formatting right from the start. Then, especially with a regex, make sure you can describe what you need to extract in plain text in a way that satisfies all examples. i.e. "extract the number right before the string Mbps (case insensitive) but before the slash as download_speed. Extract the number after the slash and right before the always capital M followed by a space as download speed". Then convert that to regex. Expect the unexpected! Handle No number found. — Gary_W, Jun 24 '20 at 13:54

Wiktor Stribiżew · Answer 1 · 2020-06-21T15:10:02.003

2

You may use

SELECT regexp_substr('My ADSL 14Mbps/2M speed','(\d+)Mbps/\d+M', 1, 1, NULL, 1) donwload_speed
      ,regexp_substr('My ADSL 14Mbps/2M speed','\d+Mbps/(\d+)M', 1, 1, NULL, 1) upload_speed
  FROM dual

See an SQLFiddle

The (\d+)Mbps/\d+M pattern matches and captures into Group 1 any one or more digits, then Mbps/, then 1+ digits and then M (the group value is extracted with the help of the last 1 argument).

The \d+Mbps/(\d+)M pattern matches any one or more digits, then Mbps/, then 1+ digits captured into Group 1 and then M.

See regex #1 demo and regex #2 demo.

edited Jun 21 '20 at 15:10

answered Jun 20 '20 at 17:01

Wiktor Stribiżew

607,720
39
448
563

Hi thanks for the sql, above SQL is working fine if the string is having numbers only in one place, but suppose if the SQL is having some number in other place it is joining, example input text is "My ADSL 2M(200) Volume 14Mbps/2M speed threshold 0M", I need only 14 and 2 that numbers within the word having forward slash – user2907940 Jun 21 '20 at 05:02
@user2907940 Have you had a chance to check my updated solution? Please provide feedback if you need more help with this. – Wiktor Stribiżew Jun 25 '20 at 09:59
Hi I have tried to execute above SQL getting error too many argument for function - oracle version Oracle Database 10g – user2907940 Jun 26 '20 at 08:24
@user2907940 It [seems to be fine](https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions131.htm), it allows these arguments. – Wiktor Stribiżew Jun 26 '20 at 09:39

score 1 · Answer 2 · answered Jun 20 '20 at 17:13

I guess that if you want to get only the numbers, so I'd do this:

SQL> col download_speed for 999999 heading "download_speed"
SQL> col upload_speed for 999999 heading "upload_speed"
SQL> SELECT regexp_replace(regexp_substr('My ADSL 14Mbps/2M speed','[^\/]+'),'[^0-9]', '') download_speed
  ,regexp_replace(regexp_substr('My ADSL 14Mbps/2M speed','[^\/]+$'), '[^0-9]', '') upload_speed FROM dual;

do u
-- -
14 2

However, if you want to transpose the columns to rows, as you show in your expected result, I would do :

    select *
from (
SELECT regexp_replace(regexp_substr('My ADSL 14Mbps/2M speed','[^\/]+'),'[^0-9]', '') download_speed
      ,regexp_replace(regexp_substr('My ADSL 14Mbps/2M speed','[^\/]+$'), '[^0-9]', '') upload_speed
  FROM dual
) unpivot include nulls ( val for col in (download_speed,upload_speed) );

COL            VA
-------------- --
DOWNLOAD_SPEED 14
UPLOAD_SPEED   2

You can change the values of COL and VA for the labels you want

Hi thanks for the sql, above SQL is working fine if the string is having numbers only in one place, but suppose if the SQL is having some number in other place it is joining, example input text is "My ADSL 2M(200) Volume 14Mbps/2M speed threshold 0M", I need only 14 and 2 that numbers within the word having forward slash — user2907940, Jun 21 '20 at 05:02

pifor · Answer 3 · 2020-06-20T17:31:15.753

1

You only need to extract the digits sequences (\d+) starting at position 1:

for download the occurence 1 of digit sequence
for upload occurence 2 of digit sequence.

Here is the SQL code:

SQL> SELECT
  2  'download_speed = ' || regexp_substr('My ADSL 14Mbps/2M speed','(\d+)', 1, 1)
  3  ||
  4  ' upload_speed = ' || regexp_substr('My ADSL 14Mbps/2M speed','(\d+)', 1 ,2)
  5  as output
  6  from dual;

OUTPUT
----------------------------------------
download_speed = 14 upload_speed = 2

SQL>

edited Jun 20 '20 at 17:31

answered Jun 20 '20 at 17:13

pifor

7,419
2
8
16

Hi thanks for the sql, above SQL is working fine if the string is having numbers only in one place, but suppose if the SQL is having some number in other place it is joining, example input text is "My ADSL 2M(200) Volume 14Mbps/2M speed threshold 0M", I need only 14 and 2 that numbers within the word having forward slash – user2907940 Jun 21 '20 at 05:02
Please edit your question to add *all* input data and *all* expected output. – pifor Jun 21 '20 at 07:05
Or better ask a new question with *all* input and *all* expected output. – pifor Jun 21 '20 at 07:17

score 0 · Answer 4 · answered Jun 21 '20 at 10:20

One option would be using REGEXP_REPLACE() which searches for digit characters with a replace_string \2 as the third argument :

SELECT REGEXP_REPLACE(str,'(.* )(\d+)(.*)','\2') AS download_speed, 
       REGEXP_REPLACE(str,'(.*\/)(\d+)(.*)','\2') AS upload_speed
  FROM tab

Demo

Oracle regexp_substr to extract data

4 Answers4