CODEWITHSUNDEEP
sqloracle-database
4 Leave Cover
4 Leave Cover

Reputation: 1276

Oracle retrieve only number in string

In Oracle pl/sql, how do I retrieve only numbers from string.

e.g. 123abc -> 123 (remove alphabet)

e.g. 123*-*abc -> 123 (remove all special characters too)

Upvotes: 0

Views: 9546

Answers (4)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627469

If you need to extract the first occurrence of one or more digits, you may simply use a REGEXP_SUBSTR:

SELECT REGEXP_SUBSTR('123*-*abc','\d+') from dual

\d+ here is NOT equal to [0-9]+ as it also matches any Unicode digit characters (like ۱۲۳۴۵۶۷۸۹).

See the DB fiddle demo:

enter image description here

Cf. \d vs [0-9]:

SELECT REGEXP_SUBSTR('۱۲۳۴۵۶۷۸۹ 123*-*abc','\d+') from dual
-- > ۱۲۳۴۵۶۷۸۹

Tested in Oracle 23c.

Upvotes: 0

tbone
tbone

Reputation: 15493

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

Upvotes: 1

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23767

your_string := regexp_replace(your_string, '\D')

Upvotes: 3

Ankit Bajpai
Ankit Bajpai

Reputation: 13527

Try this:

select regexp_replace(value, '[A-Za-z]') from dual;

Upvotes: 0

Related Questions