CODEWITHSUNDEEP
sqloracle-database
user1753142
user1753142

Reputation: 11

find letter in a column

how to query oracle to find me columns that contain numbers from 0-1000

I have tried queries like:

select (Column_name)  
from  (table_name) 
where regexp_like(CREDIT_BANK_BRANCH,'^[0-999]$');

Upvotes: 1

Views: 159

Answers (2)

Tulains Córdova
Tulains Córdova

Reputation: 2639

Use this regular expression:

^([0-9]{1,3}|1000)$

Explanation:

Regular expressions cannot evaluate numeric expressions, so you have to treat everything as characters, so:

Find any group of numbers one to three characters long: [0-9]{1,3} or just the number 1000.

Pipe means "or" and parenthesis are necesary to limit the escope of the expression.

Shorter version:

^(\d{1,3}|1000)$

Tested:

enter image description here

Upvotes: 2

Michal Klouda
Michal Klouda

Reputation: 14521

Change the regex to ^[0-9]+$ (or shorter ^\d+$) if you want only rows containing digits in that column returned.

If you want to find records with a non-digit character, then use [^0-9] (or shorter \D).

See Using Regular Expressions in Oracle Database for more details.

Upvotes: 0

Related Questions