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
– Gary_W, Commented Jun 24, 2020 at 13:54

Wiktor Stribiżew · Accepted Answer · 2020-06-21 15:10:02Z

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, 2020 at 15:10

answered Jun 20, 2020 at 17:01

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2907940 Over a year ago

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

Wiktor Stribiżew Over a year ago

@user2907940 Have you had a chance to check my updated solution? Please provide feedback if you need more help with this.

user2907940 Over a year ago

Hi I have tried to execute above SQL getting error too many argument for function - oracle version Oracle Database 10g

Wiktor Stribiżew Over a year ago

@user2907940 It seems to be fine, it allows these arguments.

Roberto Hernandez · Accepted Answer · 2020-06-20 17:13:10Z

1

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

answered Jun 20, 2020 at 17:13

Roberto Hernandez

8,5933 gold badges22 silver badges51 bronze badges

1 Comment

user2907940 Over a year ago

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

pifor · Accepted Answer · 2020-06-20 17:31:15Z

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, 2020 at 17:31

answered Jun 20, 2020 at 17:13

pifor

7,9722 gold badges12 silver badges18 bronze badges

3 Comments

user2907940 Over a year ago

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

pifor Over a year ago

Please edit your question to add all input data and all expected output.

pifor Over a year ago

Or better ask a new question with all input and all expected output.

Barbaros Özhan · Accepted Answer · 2020-06-21 10:20:26Z

0

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

answered Jun 21, 2020 at 10:20

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Collectives™ on Stack Overflow

Oracle regexp_substr to extract data

4 Answers 4

4 Comments

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related