CODEWITHSUNDEEP
sqlregexoracle-databaseregexp-substr
user2907940
user2907940

Reputation: 43

Oracle regexp_substr to extract data

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

Upvotes: 2

Views: 474

Answers (4)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626689

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.

Upvotes: 2

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

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

Upvotes: 0

pifor
pifor

Reputation: 7882

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>

Upvotes: 1

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

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

Upvotes: 1

Related Questions