Oracle numbers filtering-CodePudding

I have the following query:

SELECT distinct INCOME_LEVEL
FROM CUSTOMERS
where INCOME_LEVEL like '%-%'

Which returns:

Query output

I need to Leave only those levels that are in the format "999,999 - 999,999", where the number "9" means that any of the digits 0-9 are possible.

CodePudding user response：

You can use REGEXP_LIKE:

SELECT DISTINCT
       income_level
FROM   customers
WHERE  REGEXP_LIKE( income_level, '^\d{3},\d{3}\s*-\s*\d{3},\d{3}$' )

CodePudding user response：

Presuming that values in that column - that contain the - character - always look like 250,000 - 299,999 (i.e. have numbers, not letters or any other characters, while there's always one space between numbers), you could even try with

select distinct income_level
from customers
where income_level like '%-%'
  and length(income_level) = 17