Oracle: SQL Query That Returns Rows with Only Numeric Values

Oracle: SQL query that returns rows with only numeric values

You can use the REGEXP_LIKE function as:

SELECT X 
FROM myTable
WHERE REGEXP_LIKE(X, '^[[:digit:]]+$');

Sample run:

SQL> SELECT X FROM SO;

X
--------------------
12c
123
abc
a12

SQL> SELECT X FROM SO WHERE REGEXP_LIKE(X, '^[[:digit:]]+$');

X
--------------------
123

SQL>

Query that returns rows that start with numeric values in Oracle

i guess you are looking for this regex:

SELECT * 
FROM table_name
WHERE regexp_like(column_Name,'^[0-9]')

or in short

SELECT * 
FROM table_name
WHERE regexp_like(column_Name,'^\d')

What you did is negate the result of the elements in the bracket, the ^ needs to be before the brackets

Oracle, show only rows where no numeric values appear

You can use Regular Expressions for pattern matching in Oracle.

SELECT
*
FROM
yourTable
WHERE
NOT REGEXP_LIKE(x, '[0-9]+')

This will exclude any rows that have one or more numeric digits in column x.

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

How to find non-numeric columns containing only numeric data?

Create a function as this:

create or replace function isNumeric(val in VARCHAR2) return INTEGER AS
res NUMBER;
begin
res := TO_NUMBER(val);
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END;

Then you can use it like this:

DECLARE
r integer;
BEGIN
For aCol in (SELECT TABLE_NAME, COLUMN_NAME FROM user_tab_cols WHERE DATA_TYPE = 'VARCHAR2') LOOP
-- What about CHAR and CLOB data types?
execute immediate 'select count(*) from '||aCol.TABLE_NAME||' WHERE isNumeric('||aCol.COLUMN_NAME||') = 0' into r;
if r = 0 then
DBMS_OUTPUT.put_line(aCol.TABLE_NAME ||' '||aCol.COLUMN_NAME ||' contains numeric values only');
end if;
end loop;
end;

Note, the performance of this PL/SQL block will be poor. Hopefully this is a one-time-job only.



Related Topics



Leave a reply



Submit