2

I have searched extensively to try find a way of selecting distinct values from a given list that don't exist in a table, but there doesn't seem to be a simple way of doing this.

I was wondering if this is possible to do somehow, or is the easiest solution to put the values into a table and select distinct from there?

This is the gist of what I am trying to do:

Select distinct column_name from dual where column_name in ('one','two','two','elephant');

semiColon
  • 205
  • 6
  • 24

3 Answers3

6

The alternative for "dual joins" is the built in varchar2 array. Here is the example

select distinct column_value
from   table(
         sys.odcivarchar2list(
           'one','two','two','elephant'
         )
       )
Maxim Borunov
  • 901
  • 5
  • 9
0

The constructs with in won't work, unless you have a query that returns all values you specify and maybe a bit more, so if you have a set of letters, you could input it in a query that returns all letters. That would give you something like this:

select
  *
from (
  select chr(96 + level) as letter
  from dual
  connect by level <= 26)
where
  letter in ('a', 'a', 'b');

Of course, that would work for fixed limited sets, like the whole alphabet, or even numbers from 1 to 1000, but it's not a way to convert any set to a distinct list.

Alternative: dual joins

You can select a single value using the fake table dual. You can even make a union of such selects, although it looks a bit cumbersome:

select 'a' as MyValue from dual
union select 'a' from dual
union select 'b' from dual

If you use union instead of union all, the query will implicitly return distinct values only.

Alternative: Split string

If you have the values in a string, you could split that string and return the items. There are various ways to do that, but not out of the box. You could have a look at the question Splitting string into multiple rows to which the answers show various solutions.

GolezTrol
  • 114,394
  • 18
  • 182
  • 210
  • Thank you for you answer, but I've edited the question to better reflect what I am trying to do. The dataset will be large and made up of many varchar words rather than individual letters, I think the methods you have shown above might be more effort than it would be worth but thank you for showing that method! – semiColon Jan 26 '19 at 10:48
  • Jup, i'd look at the alternative solutions or the other question then. – GolezTrol Jan 26 '19 at 12:00
0

Try something like this:

select distinct trim(regexp_substr('a,b,b,c','[^,]+', 1, level) ) value
from dual
connect by regexp_substr('a,b,b,c', '[^,]+', 1, level) is not null
hbourchi
  • 309
  • 2
  • 8