I have a table in Oracle 12c Standard Edition Release 12.2.0.1.0 with the following sample data
| SECTION | PARENT | CHILD |
|---|---|---|
| A | FIRST_ENTRY | STRING1/AA1 |
| A | FIRST_ENTRY | STRING2 |
| A | FIRST_ENTRY | STRING3 |
| A | STRING1/AA1 | SUB_STRING1 |
| A | STRING1/AA1 | SUB_STRING2 |
| A | STRING1/B2 | SUB_STRING4 |
| A | STRING1/B2 | SUB_STRING5 |
| A | STRING5 | SUB_STRING5 |
| ... | ... | ... |
When I execute the below query with Oracle Developer Tools for VS Code:
SELECT
SAMPLE_TABLE.PARENT,
SAMPLE_TABLE.CHILD
FROM
SAMPLE_TABLE
WHERE
SAMPLE_TABLE.SECTION1 = 'A'
START WITH
SAMPLE_TABLE.PARENT = 'FIRST_ENTRY'
CONNECT BY PRIOR
SAMPLE_TABLE.CHILD = SAMPLE_TABLE.PARENT
I get the below result.
| PARENT | CHILD |
|---|---|
| FIRST_ENTRY | STRING1/AA1 |
| FIRST_ENTRY | STRING2 |
| FIRST_ENTRY | STRING3 |
| STRING1/AA1 | SUB_STRING1 |
| STRING1/AA1 | SUB_STRING2 |
| STRING1/B2 | SUB_STRING4 |
| STRING1/B2 | SUB_STRING5 |
STRING1/B2 should not be in the result and it seems to me that the query result is matching on STRING1 excluding the forward slash and what is after it. When I try the query with strings that do not contain forward slash the result is as expected.
I went through the documentation for the CONNECT BY query, but did no find any solution there.
How to write the above query in a way that matches parent/child taking in account the forward slash and string after it?