duplicate oacle sql value

Question

I have an ENTITY field with different values like this:

Orange/OBS/SCE/CSO/ESC/STI/CSE/TE
Orange/ODT/GSE
Orange/FGI
Orange/DSE/FGE/CSO/

If we take the first example:

Orange OBS/SCE/CSO/ESC/STI/CSE/TE

I would like to have this result in the end :

ORANGE
ORANGE/OBS
ORANGE/OBS/SCE
ORANGE/OBS/SCE/CSO
ORANGE/OBS/SCE/CSO/ESC/STI
Orange/OBS/SCE/CSO/ESC/STI/CSE
Orange/OBS/SCE/CSO/ESC/STI/CSE/TE

I wrote the following SQL query, but I get more duplicates instead of having a single value:

select
  substr( 'Orange/OBS/SCE/CSO/ESC/STI/CSE/TE', 1 , 
    INSTR( 'Orange/OBS/SCE/CSO/ESC/STI/CSE/TE', '/' , -level)) AS DECOMPOSITION_ENTITY
from (select distinct 'Orange/OBS/SCE/CSO/ESC/STI/CSE/TE' from dual )
connect by INSTR( 'Orange/OBS/SCE/CSO/ESC/STI/CSE/TE', '/', -level ) >0

I get the following results:

DECOMPOSITION_ENTITY
Orange/OBS/SCE/CSO/ESC/STI/CSE/
Orange/OBS/SCE/CSO/ESC/STI/CSE/
Orange/OBS/SCE/CSO/ESC/STI/CSE/
Orange/OBS/SCE/CSO/ESC/STI/
Orange/OBS/SCE/CSO/ESC/STI/
Orange/OBS/SCE/CSO/ESC/STI/
Orange/OBS/SCE/CSO/ESC/STI/
Orange/OBS/SCE/CSO/ESC/
Orange/OBS/SCE/CSO/ESC/
Orange/OBS/SCE/CSO/ESC/
Orange/OBS/SCE/CSO/ESC/
Orange/OBS/SCE/CSO/
Orange/OBS/SCE/CSO/
Orange/OBS/SCE/CSO/
Orange/OBS/SCE/CSO/
Orange/OBS/SCE/
Orange/OBS/SCE/
Orange/OBS/SCE/
Orange/OBS/SCE/
Orange/OBS/
Orange/OBS/
Orange/OBS/
Orange/OBS/
Orange/
Orange/
Orange/
Orange/

The Orange / should appear only once, the same for the Orange / OB, so on, I should have a value only once for each result.

Suddenly I do not block because I do not know what is wrong with my request.

Thank you in advance for your help :)

Please fix your question by clearly showing sample data in tabular form. — Tim Biegeleisen, Oct 30 '20 at 09:09

score 1 · Answer 1 · answered Oct 30 '20 at 09:51

You can use the following query:

select DECOMPOSITION_ENTITY
from (select INSTR( x, '/', -level ) inst, substr(x,-level,1) ch,
substr( x,1, INSTR( x, '/' , -level)) AS DECOMPOSITION_ENTITY ,level
from (select 'Orange/OBS/SCE/CSO/ESC/STI/CSE/TE' x from dual )
connect by INSTR( x,'/', -level ) >0
) where ch='/'
/

The result is

DECOMPOSITION_ENTITY
--------------------------------------------------
Orange/OBS/SCE/CSO/ESC/STI/CSE/
Orange/OBS/SCE/CSO/ESC/STI/
Orange/OBS/SCE/CSO/ESC/
Orange/OBS/SCE/CSO/
Orange/OBS/SCE/
Orange/OBS/
Orange/

To see why you get the result you show, run the inner query from the one I show above. You'll see that you substr is ok, but it is giving you the same result for all values of level, while you want only the cases where the 'ch' column I use must be '/'.

score 0 · Answer 2 · answered Oct 30 '20 at 14:41

Here's another way using REGEXP_SUBSTR(). As you know, CONNECT BY "loops" for each element based on the delimiter "/". For each of those iterations, it returns from the start of the string until the delimiter of the count of the iteration its on, then just uses trim() to get rid of the trailing "/".

WITH tbl(str) AS (
  SELECT 'Orange/OBS/SCE/CSO/ESC/STI/CSE/TE' FROM dual
)
SELECT RTRIM(REGEXP_SUBSTR(str, '(([^/]*(/|$)){' || LEVEL || '})', 1, 1, NULL, 1), '/') STRING
FROM tbl
CONNECT BY LEVEL <= REGEXP_COUNT(str, '/') + 1;


STRING                           
---------------------------------
Orange                           
Orange/OBS                       
Orange/OBS/SCE                   
Orange/OBS/SCE/CSO               
Orange/OBS/SCE/CSO/ESC           
Orange/OBS/SCE/CSO/ESC/STI       
Orange/OBS/SCE/CSO/ESC/STI/CSE   
Orange/OBS/SCE/CSO/ESC/STI/CSE/TE

8 rows selected.

duplicate oacle sql value

2 Answers2