Returning text within a string in Oracle SQL -

i have string data in column of table contain monetary amount in somewhere.

e.g. column may contain like: "dave once paid £50.00 lottery syndicate" "total investment returns fund in 2017 came £150,964.39"

how can search occurrence of '£' sign , return number occurs after it?

thanks

here 1 way. search expression bit complicated because must allow thousand separators , decimal points, optional. assumes "western" usage of thousands separators - have modified allow lakh (indian) notation, example. produce null when there no pound sign, or if there pound sign not followed @ least 1 digit. (so have modified if allow things £.60 instead of £0.60.) can capture amount (without currency symbol) if desired - slight modification use of regexp_substr (use capture groups).

the biggest change needed if may have more 1 amount per input row.

with      inputs ( str ) (        select 'dave once paid £50.00 lottery syndicate.' dual union        select 'total returns in 2017 came £150,964.39.'    dual      ) -- end of simulated inputs (for testing purposes only, not part of solution). -- use actual table , column names in sql query below. select str, regexp_substr(str, '£\d{1,3}(,?\d{3})*(\.\d+)?') amount   inputs ;  str                                            amount ---------------------------------------------  ----------- dave once paid £50.00 lottery syndicate.  £50.00 total returns in 2017 came £150,964.39.     £150,964.39 

edit

in comment below, op asked how obtain amount, without currency symbol. easiest way use capture groups directly in regexp_substr() function. version below uses 6 arguments function: before first input string , second search pattern. third , forth starting position , occurrence (both equal 1 problem). fifth, null, special options don't need. sixth argument relevant: 1 means return first capture group, i.e. part of search pattern included in first pair of matching parentheses (counting left right). notice additional pair of parentheses in search pattern, isolate amount pound symbol:

regexp_substr(str, '£(\d{1,3}(,?\d{3})*(\.\d+)?)', 1, 1, null, 1) 

edit #2

to extract amount in number data type, not necessary remove pound sign; to_number() function can handle it. instead, substring pound sign followed amount must wrapped within to_number(), using proper format model , explicit currency symbol:

to_number(regexp_substr(str, '£\d{1,3}(,?\d{3})*(\.\d+)?'),                 'l999,999,999,999,999.000000', 'nls_currency=£') 

just make sure include enough digits on right of decimal point accommodate possible amounts. (too many digits in format model never problem.)