Extract Number from String With Oracle Function

Extract number from string with Oracle function

You'd use REGEXP_REPLACE in order to remove all non-digit characters from a string:

select regexp_replace(column_name, '[^0-9]', '')
from mytable;

or

select regexp_replace(column_name, '[^[:digit:]]', '')
from mytable;

Of course you can write a function extract_number. It seems a bit like overkill though, to write a funtion that consists of only one function call itself.

create function extract_number(in_number varchar2) return varchar2 is
begin
  return regexp_replace(in_number, '[^[:digit:]]', '');
end; 

Oracle retrieve only number in string

Several options, but this should work:

select regexp_replace('123*-*abc', '[^[:digit:]]', '') from dual

This removes all non-digits from the input.

If using in pl/sql, you could do an assignment to a variable:

declare
    l_num number;
    l_string varchar2(20) := '123*-*abc';
begin
    l_num := regexp_replace(l_string, '[^[:digit:]]', '');
    dbms_output.put_line('Num is: ' || l_num);
end;

Output:

Num is: 123

Extract integer from a string in Oracle SQL and find and replace a specific character with another

To extract only number part please use '[^[:digit:]]' instead of '[[:alpha:]]' with regexp_replace()

regexp_replace(column_name, '[^[:digit:]]', '')

You can use below query to resolve your second problem.

select '"'||replace('Example"','"','""')||'"' from yourtable

Extract numbers from string and multiply each digit oracle SQL

xmlquery can do this easily:

select xmlcast(xmlquery('1*2*3*4' returning content) as number) as result
from   dual
;


RESULT
------
    24

The string doesn't have to be hard-coded - it can be a column name, or any other string expression that returns a valid arithmetic expression. (Note that if you have division, you would need to replace / with div - for example by using the replace function.)

Oracle: Extract number from String

"REGEXP_REPLACE extends the functionality of the REPLACE function by letting you search a string for a regular expression pattern. By default, the function returns source_char with every occurrence of the regular expression pattern replaced with replace_string." from Oracle docu

You could use, e.g.,

SELECT REGEXP_REPLACE('Stores selling COD4 in 400 Miles', '^.*?(\d+ ?MILES).*$', '\1', 1, 0, 'i') FROM DUAL;

or alternatively

SELECT REGEXP_SUBSTR('Stores selling COD4 in 400 Miles', '(\d+ ?MILES)', 1, 1, 'i') FROM DUAL;

extract number from string in Oracle

The last parameter of REGEXP_SUBSTR() indicates the sub-expression you want to pick. In this case you can't just match 30 then some more numbers as the second set of digits might have a 30. So, it's safer to match the following, where x are more digits.

SO# 30xxxxxx

As a regular expression this becomes:

SO#\s30\d+

where \s indicates a space \d indicates a numeric character and the + that you want to match as many as there are. But, we can use the sub-expression substringing available; in order to do that you need to have sub-expressions; i.e. create groups where you want to split the string:

(SO#\s)(30\d+)

Put this in the function call and you have it:

regexp_substr(str, '(SO#\s)(30\d+)', 1, 1, 'i', 2)

SQL Fiddle

oracle regular expression to extract decimal numbers from string

Use REGEXP_SUBSTR:

SELECT REGEXP_SUBSTR(col, '-?\d+(\.\d+)?')
FROM yourTable;

This pattern also makes the decimal component optional.

---

Related Topics