How to find all entries that match part of a string criteria but not another in Oracle SQL

Question

I have a column like:

Values
111.111.111-Dummy
111.111.111-Dummy2
111.111.111-X
222.222.222-Dummy
222.222.222-Dummy2
333.333.333-Dummy
333.333.333-Dummy2
333.333.333-X

I need to find the numbers that do not have an entry with "-X" in the end. So in this scenario the query should show: 222.222.222. My idea so far was to first trim the results to only have the numbers part (or everything before the '-') But I don't know what to do next. How can I find entries that don't match in the same column and same table?

Answer 1

select substr(values_, 1, instr(values_, '-') - 1) as numbers
from   {your-table}
group  by substr(values_, 1, instr(values_, '-') - 1)
having count(case when values_ like '%-X' then 1 end) = 0;

values is a reserved keyword in Oracle, and therefore it can't be an identifier (such as a column name); I changed it by adding a trailing underscore.

Note that this assumes all "values" are followed by a dash and a (possibly empty) string. If you may also have values like 111.11.1111 (with no dash at the end) then the query must be modified slightly, but I assumed there aren't any - otherwise you should have included one or two in your sample.

Answer 2

Use not like in a having clause:

select substring_index(values, '-', 1)
from t
group by substring_index(values, '-', 1)
having sum(values like '%-x') = 0;