Oracle SQL compare strings and find matching sub-strings

Question

I have colon separated tags associated to two different entities in two tables. Would like to do a sub-string matching for the tags and relate the entities.

Table 1 - Table of issues

Issue ---------------- Tag

Issue 1 -------------- Dual UOM:Serial Control:Material Issue

Issue 2 -------------- Validity rule:Effectivity date


Table 2 - Table of Tests


Test ----------------- Tag

Test 1 --------------- Inventory:Outbound:Material Issue

Test 2 --------------- Items:Single UOM

Test 3 --------------- Items:Dual UOM

Test 4 --------------- Recipe:Validity Rule

Test 5 --------------- Formula:Version control:date

Test 6 --------------- Formula:Effectivity date

Now, for each issue in table 1, I need to compare its associated tag with the tags in table 2 and find applicable tests.

In above ex,

Issue 1 - Matched tests will be Test 1, Test 3

Issue 2 - Matched tests will be Test 4, Test 5

All the tags associated to the issues and tests will come from a common tag master.

Any help in providing the sql code snippet that would do this sub-string to sub-string matching is much appreciated.

Hi, What have you tried? You will get more answers if you put some code demonstrating your approach. — mnesarco
– mnesarco, Commented Aug 17, 2020 at 20:15
The whole point of the relational model is to avoid multi-value fields. I'm not sure why you are using Oracle if you can't define this kind of data in normalised tables. — William Robertson
– William Robertson, Commented Aug 17, 2020 at 22:15

Littlefoot · Accepted Answer · 2020-08-17 19:50:23Z

Here's one option: split issues into rows and compare them to test tags, using the INSTR function. Note that letter case must match. If it doesn't (in reality), use lower or upper function.

Read comments within code (which is split into several parts, to improve readability).

Sample data first:

SQL> with
  2  -- sample data
  3  issues (issue, tag) as
  4    (select 1, 'Dual UOM:Serial Control:Material Issue' from dual union all
  5     select 2, 'Validity Rule:Effectivity date'         from dual
  6    ),
  7  tests (test, tag) as
  8    (select 1, 'Inventory:Outbound:Material Issue' from dual union all
  9     select 2, 'Items:Single UOM'                  from dual union all
 10     select 3, 'Items:Dual UOM'                    from dual union all
 11     select 4, 'Recipe:Validity Rule'              from dual union all
 12     select 5, 'Formula:Version control:date'      from dual union all
 13     select 6, 'Formula:Effectivity date'          from dual
 14    ),

Split issues into rows (splitiss), compare them to test tags (temp)

 15  -- split issues into rows ...
 16  splitiss as
 17    (select issue,
 18            tag,
 19            regexp_substr(tag, '[^:]+', 1, column_value) val
 20     from issues cross join
 21       table(cast(multiset(select level from dual
 22                           connect by level <= regexp_count(tag, ':') + 1
 23                          ) as sys.odcinumberlist))
 24    ),
 25  -- ... and now compare them to test tags
 26  temp as
 27    (select i.issue, i.tag issue_tag, i.val, t.test, t.tag test_tag,
 28       instr(t.tag, i.val) ins
 29     from splitiss i cross join tests t
 30    )

Return the result:

 31  -- return only test tags which match to separate issues (INS > 0)
 32  select t.issue,
 33         t.issue_tag,
 34         listagg(t.test, ', ') within group (order by t.test) matched_tests
 35  from temp t
 36  where t.ins > 0
 37  group by t.issue, t.issue_tag;

     ISSUE ISSUE_TAG                              MATCHED_TESTS
---------- -------------------------------------- --------------------
         1 Dual UOM:Serial Control:Material Issue 1, 3
         2 Validity Rule:Effectivity date         4, 6

SQL>

P.S. I believe you posted wrong test tags for issue #2; should be 4, 6, not 4, 5.

Gopal Krishnan · Accepted Answer · 2020-08-18 09:29:44Z

0

Thanks, this worked I did break the tags into rows and then used substr,instr matching.

answered Aug 18, 2020 at 9:29

Gopal Krishnan

1

Collectives™ on Stack Overflow

Oracle SQL compare strings and find matching sub-strings

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related