CODEWITHSUNDEEP
sqloracle-database
nodev_101
nodev_101

Reputation: 109

I need to display only numerical value from a varchar2 datatype column without using any regex

Table name SAMP_TAB - it has only one column COL of datatype varchar2 - with data like this:

COL
----
1234
abcd
098
12wer
345

These are the above sample values in that column.

Now I want to write a query which will return only numeric values from this column.

Desired output:

1234
098
345

I want to write that query without using any regex.

Upvotes: 2

Views: 81

Answers (4)

APC
APC

Reputation: 146239

If you're using 12c R2 or later (and you should be) you can use the built-in function validate_conversion() which is a safe way of determining whether a value can be converted to another datatype. Find out more.

Like this:

select * from samp_table
where validate_conversion(col as number) = 1
/

This approach has a major advantage over solutions using regex or translate() in that it tests whether the string evaluates to a numeric value, whereas the other solutions would choke on, say, an exponential expression like 1.234E+10. Or might pass an IP address like 127.0.0.1.

Here's a demo on db<>fiddle.

If you have an earlier version of Oracle you can write a user-defined function which uses to_number() to evaluate a passed parameter:

create or replace function is_number (p_str in varchar2) return pls_integer
is
  rv pls_integer;
begin

  declare
    n number;
  begin

    n := to_number(p_str);
    rv := 1; -- p_str is a number

  exception
    when others then
      rv := 0; -- p_str is not a number
  end;

  return rv;

end;
/

You would call this function in the WHERE clause same as validate_conversion() in the example above.

Upvotes: 4

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65278

Splitting each character, extracting integers through filtering by ascii() function as ascii(<character>) between 48 and 57, and recombining the strings might be another option :

with t as
(      
 select substr(col,level,1) as chr, level as lvl,
        row_number() over (order by 0) as rn
   from samp_tab
  connect by level <= length(col)
      and prior sys_guid() is not null
      and prior col = col
), t2 as
(
select t.*, sum(case when lvl=1 then 1 else 0 end) over (order by rn) as rn2 
  from t
 where ascii(chr) between 48 and 57
)
select listagg(chr) within group (order by rn2) as new_col
  from t2
 group by rn2;

Demo

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

Assuming you can't use regex, for some reason:

SELECT COL
FROM SAMP_TAB
WHERE TRIM(TRANSLATE(COL, '0123456789', ' ')) IS NULL;

If you can use regex, then use REGEXP_LIKE with the pattern ^[0-9]+$:

SELECT COL
FROM SAMP_TAB
WHERE REGEXP_LIKE (COL, '^[0-9]+$');

Demo

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142743

Alternatively:

SQL> with test (col) as
  2    (select '1234'  from dual union all
  3     select 'abcd'  from dual union all
  4     select '098'   from dual union all
  5     select '12wer' from dual union all
  6     select '1ab3'  from dual union all
  7     select '345'   from dual
  8    )
  9  select col
 10  from test
 11  where translate(col, 'a0123456789', 'a') is null;

COL
-----
1234
098
345

SQL>

Upvotes: 0

Related Questions