CODEWITHSUNDEEP
sqloracle-database
Rakesh119
Rakesh119

Reputation: 43

extract a word after a word from a string

I have a big note column in which there is a word NUMBER_OF_SERVERS_03 the 03 can be any 2 digits or it can have n digits and there is digits after that for example NUMBER_OF_SERVERS_03 2 i want to extract the digits after that for eg

Notes Column - 'asdasd asdasda asdasd NUMBER_OF_SERVERS_03 24 hkashii rwnijt'
output will should be 24

Thanks Rakesh

Upvotes: 0

Views: 47

Answers (2)

Popeye
Popeye

Reputation: 35900

You need to use REGEXP_SUBSTR with match parameters as follows:

SQL> SELECT
  2      REGEXP_SUBSTR('asdasd asdasda asdasd NUMBER_OF_SERVERS_03 24 hkashii rwnijt',
  3      'NUMBER_OF_SERVERS_[0-9]+\s+([0-9]+)', 1, 1, NULL, 1) as RESULT
  4  FROM
  5      DUAL;

RE
--
24

-- Example with spaces and different server number
SQL> SELECT
  2      REGEXP_SUBSTR('asdasd asdasda asdasd NUMBER_OF_SERVERS_11   10 hkashii rwnijt',
  3      'NUMBER_OF_SERVERS_[0-9]+\s+([0-9]+)', 1, 1, NULL, 1) as RESULT
  4  FROM
  5      DUAL;

RE
--
10

SQL>

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can use regexp_replace():

select regexp_replace(notes, '^.*NUMBER_OF_SERVERS_[0-9]+[^0-9]*([0-9]+).*$', '\1')
from (select 'asdasd asdasda asdasd NUMBER_OF_SERVERS_03 24 hkashii rwnijt' as notes from dual)

If you know that the number follows that string with a space, then you can slightly simplify this to:

select regexp_replace(notes, '^.*NUMBER_OF_SERVERS_[0-9]+ ([0-9]+).*$', '\1')

Upvotes: 0

Related Questions