Oracle SQL Regex Matching two fields

First of all, the matching pattern you mentioned… I am not sure why I can’t understand it. The way you described,

The determination of matching is just A followed by a nonzero, B
followed by any nonzero, C followed by any nonzero

this should also not match the row 1 of table 1 because A is followed by some zeros, but in table 2 it’s not the case.

So I need more explanation if this answer doesn’t help you.

As you mentioned:

The two fields in the separate tables have various ways of being
formatted which is not ideal

An idea would be to try normalizing these 2 fields, so that the ones that don’t match with each other. Check this out:

-- CTEs to normalize the strings 
--and REGEXP_REPLACE to remove all non-alphanumeric characters from EXAMPLE_STRING
WITH NormalizedTable1 AS (
  SELECT ID, REGEXP_REPLACE(EXAMPLE_STRING, '[^A-Za-z0-9]', '') AS NormalizedString
  FROM Table1
),
NormalizedTable2 AS (
  SELECT ID, REGEXP_REPLACE(EXAMPLE_STRING, '[^A-Za-z0-9]', '') AS NormalizedString
  FROM Table2
)
-- Finally, join the normalized tables and select non-matching records
SELECT t1.ID, t1.EXAMPLE_STRING AS Table1String, t2.EXAMPLE_STRING AS Table2String
FROM NormalizedTable1 t1
INNER JOIN NormalizedTable2 t2 ON t1.ID = t2.ID
WHERE t1.NormalizedString != t2.NormalizedString;

You should choose your RegEx for normalization very carefully, to avoid surprises.

As I said, I couldn’t really understand that part of your question, so my RegEx probably won’t be an exact fix to your problem

Good Luck!