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
Rails: Get Next/Previous Record
How to Do a Simple 'Find and Replace" in Mssql
How to Check If an SQL Result Contains a Newline Character
Calling a Stored Procedure in Oracle with in and Out Parameters
Why Can't I Simply Add an Index That Includes All Columns
Mysql: Full Outer Join - How to Merge One Column
Exporting Blob from MySQL Database to File with Only SQL
Flatten Adjacency List Hierarchy to a List of All Paths
Select Where Count of One Field Is Greater Than One
Pyspark: Filter Dataframe Based on Multiple Conditions
SQL Server Equivalent to Oracle's Nulls First
Postgresql Calculate Difference Between Rows
How to Group MySQL Rows with Same Column Value into One Row
Oracle - Why Does the Leading Zero of a Number Disappear When Converting It To_Char