This content has been marked as final. Show 2 replies
Here's one way:
You'll notice that this is identical to the solution for your previous question, except that the HAVING clause has changed, and since the HAVING clause now references l.loss, I added l.loss to the GROUP BY clause.
WITH targets AS ( SELECT '%/' || REGEXP_SUBSTR ( :str , '[^:]+' , 1 , LEVEL ) || '/%' AS target , target_cnt FROM ( SELECT LENGTH ( :str || 'ab') - LENGTH (REPLACE (:str, ':') || 'a') AS target_cnt FROM dual ) CONNECT BY LEVEL <= target_cnt ) SELECT l.key FROM limbs l JOIN targets t ON '/' || l.loss || '/' LIKE t.target GROUP BY l.key, l.loss HAVING COUNT (*) = 1 + LENGTH ( l.loss ) - LENGTH (REPLACE (l.loss, '/')) ;
This assumes that the items in :str and in l.loss are unique. That is, if the user enters 'eye:eye', it will match l.keys 4 and 6, because those keys match 2 times, and there are 2 items in l.loss. If that's a problem, the query above will have to changed a little, but only a little.
The HAVING clause now says that the number of items that match must be the number of items in l.loss. The way the number of items in l.loss is computed is similar to the way the number of items in the parameter :str is computed. However, the earlier query went to a little trouble in case :str was :NULL. I'm not sure that's necessary, and I assume it's not necessary for l.loss, either.