CODEWITHSUNDEEP
oracle-databaseparsingstored-proceduresplsql
Aravindhan
Aravindhan

Reputation: 15

Find value for a key in a string

Query:

Select searchtext from xyz.

Result

searchtext
-------------------------------------------------------------------------------
serviceID 100615188 accessID 100615187 accessPathSpeed 5Mbps handoverID 100556732 accessSpeed 5Mbps

In this I want serviceID as column name and 100615188 as value. like

ServiceID
-----------
100556732

Upvotes: 0

Views: 77

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

You can use regular expressions to solve this:

select regexp_replace(col,'.*serviceID\s+(\d+).*','\1') from your_table;

Demo:

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
SQL> with your_table(col) as (
  2    select 'serviceID 100615188 accessID 100615187 accessPathSpeed 5Mbps handoverID 100556732 accessSpeed 5Mbps' from dual
  3  )
  4  select
  5    regexp_replace(col,'.*serviceID\s+(\d+).*','\1') serviceId
  6  from your_table;

SERVICEID
---------
100615188

SQL>

Details:

'.*serviceID\s+(\d+).*'
  • .* - Match zero or more character before serviceID
  • serviceID - match the text literally
  • \s+ - match 1 or more whitespace character after serviceID
  • () - to build a capturing group
  • \d+ - match 1 or more digits

and in the replace part

  • \1 - replace the whole match with the first captured group using backreferencing.

If the serviceId is always going to be the first integer part in the string, then you can also use regexp_substr:

select
  regexp_substr(col,'\d+') serviceId
from your_table;

Upvotes: 1

Related Questions