Getting all posible values which are not in the same column but with specified conditions

Question

I trying to get all values from table with this conditions:

length of value is 3
value is not in values which are longer that 3

Table example (simple_table):

id | name
-------------
1  | 418
2  | 223:542
3  | 54
4  | 418
5  | 418:223:100
6  | 223
7  | 999
8  | 132
9  | null
10 | 100

So I have three different classes of length

length of name is 3
length of name is less than 3
length of name is more than 3

My code:

First

select distinct name 
from simple_table
where name is not null  
and LENGTH( name ) = 3;

Return values

name
-------
418
223
999
132

Second:

select distinct name 
from simple_table
where name is not null  
and LENGTH( name ) > 3;

Return values

name
-------
223:542
418:223:100

"Main part" of the code

select distinct name 
from simple_table
where name is not null  
and LENGTH( name ) = 3
and '223:542' not like CONCAT(CONCAT('%', name ), '%');

This return

name
-------
418
999
132
100

When I try to use this code, it cause error

select distinct name
from simple_table
where name is not null  
and LENGTH( name) = 3
and (select distinct name
from simple_table
where name is not null
and LENGTH( name) > 3) not like in (CONCAT(CONCAT('%', name), '%'));

Error:

ORA-00936: missing expression
00936. 00000 -  "missing expression"
*Cause:    
*Action:
Error at Line: 15 Column: 50

My desired result is:

name
-------
999
132

if this was written in t-sql - you would have to take out the 'IN' operator in the last line - not sure if this will be true for Oracle as well — Taps, May 24 '19 at 13:31
First of all, this is not a good way to store these numbers and the design of this is causing 99% of your problem. Secondly, the error you are getting is coming from `LENGTH( name) > 3) not like in (CONCAT(CONCAT('%', name), '%'));` This is incorrect syntax, but there's little point in fixing it as it will not at all do what you want. In order to split that string, take a look at this thread: https://stackoverflow.com/questions/26878291/split-string-by-delimiter-position-using-oracle-sql — Barry Piccinni, May 24 '19 at 13:39

DVK · Accepted Answer · 2019-05-24T17:34:42.647

What you need to do is to do an EXIST subquery to filter out things that are listed in long strings, and do string match coupled with separator for that subquery.

SELECT distinct name
FROM   simple_table t1
WHERE  name IS NOT null  
  AND LENGTH(name) = 3
 AND NOT EXISTS (
    SELECT 1
    FROM   simple_table t2
    WHERE  (    t2.name LIKE '%' || ':' || t1.name || '%'  
            OR  t2.name LIKE '%' || t1.name || ':' || '%'  
           )
)

Please note that this isn't exactly bug free - e.g. it will match "22" to "222:3333". If you want to be more precise, you need to amend the query to something like "%:X:%" OR "%:X" OR "X:%"

Here's the setup:

CREATE TABLE simple_table (  
  name VARCHAR2(20) NULL
);

INSERT INTO simple_table VALUES ('222');
INSERT INTO simple_table VALUES ('123');
INSERT INTO simple_table VALUES ('334');
INSERT INTO simple_table VALUES ('001');
INSERT INTO simple_table VALUES ('002');
INSERT INTO simple_table VALUES ('334:555');
INSERT INTO simple_table VALUES ('334:001');
INSERT INTO simple_table VALUES ('777:002');

And result is (from SQL Fiddle http://sqlfiddle.com/#!4/5f88f/16 )

222
123

score 2 · Answer 2 · answered May 24 '19 at 14:23

Following the instructions in the thread I mentioned in the comments, I have the following solution:

SELECT t1.name 
FROM myTable t1
WHERE LENGTH(name) = 3
AND t1.name NOT IN (
     SELECT DISTINCT REGEXP_SUBSTR(t2.name, '[^:]+', 1, LEVEL) AS data
     FROM myTable t2
     WHERE LENGTH(t2.name) > 3
     CONNECT BY REGEXP_SUBSTR(t2.name, '[^:]+', 1, LEVEL) IS NOT NULL
);

Here is a demo of this in action: SQLFiddle

score 1 · Answer 3 · answered May 24 '19 at 14:06

Use not exists, but I would phrase it like this:

SELECT DISTINCT st.name
FROM simple_table st
WHERE LENGTH(st.name) = 3 AND
      NOT EXISTS (SELECT 1
                  FROM simple_table st2
                  WHERE LENGTH(st2.name) > 3 AND
                        ':' || st2.name || ':' LIKE ':%' || st.name || ':%'
                 );

By adding the delimiter to the beginning and end of the string, you account for the names at the beginning and end of the longer string.

Getting all posible values which are not in the same column but with specified conditions

3 Answers3