CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Stu
Stu

Reputation: 249

Comparing column string values in oracle

I have table in which I need to compare the values from two columns.

Col1      Col2        Col3
------------------------------------
1         sssXYZ2121  XYZ

Now a match may not be a perfect word to word match.

E.g. of a match

Col2    Col3
-----   --------------------
XYZ     XYZ                  (word to word match)
XYZ     xyz                  (can be case insensitive)
XYZ     gxyzf                (need to search within a string )
XYZ     xyzjjjjjjjjj         (need to search within a string )
XYZ     gggggXYZ             (need to search within a string )
XYZ     Xavier Yellow Zebra  (Match the full form of the abbreviation)

Now I need to find all the rows in which the value in Col3 do not match Col2. I am using Oracle 10g.

Upvotes: 2

Views: 33824

Answers (3)

MT0
MT0

Reputation: 167972

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE ABBREVIATIONS(
  Short VARCHAR2(10)   CONSTRAINT ABBR__S__PK PRIMARY KEY
                       CONSTRAINT ARRB__S__CHK CHECK( Short = UPPER( Short ) ),
  Value  VARCHAR2(100) CONSTRAINT ABBR__V__U UNIQUE
                       CONSTRAINT ABBR__V__CHK CHECK( Value IS NOT NULL AND Value = UPPER( Value ) )
);

INSERT INTO Abbreviations VALUES ( 'XYZ', 'XAVIER YELLOW ZEBRA' );

CREATE TABLE Tests ( Col1, Col2, Col3 ) AS
          SELECT 1, 'XYZ', 'XYZ' FROM DUAL
UNION ALL SELECT 2, 'xyz', 'XYZ' FROM DUAL
UNION ALL SELECT 3, 'XYZ', 'xyz' FROM DUAL
UNION ALL SELECT 4, 'xyz', 'xyz' FROM DUAL
UNION ALL SELECT 5, 'xyz', 'abcdXYZ' FROM DUAL
UNION ALL SELECT 6, 'xyz', 'XYZefg' FROM DUAL
UNION ALL SELECT 7, 'xyz', 'ghiXYZjkl' FROM DUAL
UNION ALL SELECT 8, 'xyz', 'XaViEr YelloW ZeBrAXXYYZZ' FROM DUAL
UNION ALL SELECT 9, 'Xavier Yellow Zebra', 'XXYZZ' FROM DUAL
UNION ALL SELECT 10, 'xyz', 'xy -- Not a match -- z' FROM DUAL;

Query 1:

SELECT *
FROM   Tests t
WHERE  UPPER( Col3 ) LIKE '%' || UPPER( Col2 ) || '%'
OR     EXISTS (
  SELECT 'X'
  FROM   ABBREVIATIONS a
  WHERE  (   UPPER( t.col2 ) = a.Short
          OR UPPER( t.Col2 ) = a.Value )
  AND    REPLACE( UPPER( Col3 ), a.Short, a.Value ) LIKE '%' || a.Value || '%'
)

Results:

| COL1 |                COL2 |                      COL3 |
|------|---------------------|---------------------------|
|    1 |                 XYZ |                       XYZ |
|    2 |                 xyz |                       XYZ |
|    3 |                 XYZ |                       xyz |
|    4 |                 xyz |                       xyz |
|    5 |                 xyz |                   abcdXYZ |
|    6 |                 xyz |                    XYZefg |
|    7 |                 xyz |                 ghiXYZjkl |
|    8 |                 xyz | XaViEr YelloW ZeBrAXXYYZZ |
|    9 | Xavier Yellow Zebra |                     XXYZZ |

Upvotes: 0

Fls'Zen
Fls'Zen

Reputation: 4654

Perhaps something like this will work for you. The where clause has two conditions, only one of which need to be met. The first condition is satisfied when the Col3 value is found within Col2, ignoring case. The second condition is satisfied when Col2 is the expanded version of Col3.

SELECT Col1, Col2, Col3 FROM Tbl1
LEFT JOIN Abbr ON Col3 = Abbr.Key
WHERE INSTR(UPPER(Col2), UPPER(Col3)) > 0 OR Col2 = Abbr.ExpandedName

If you wanted, you could change the second condition to allow Col2 to contain the expanded name instead of exactly be the expanded name by following how the first condition works.

Upvotes: 0

Randy
Randy

Reputation: 16677

XYZ, XYZ (word to word match)

col2 = col3

XYZ, xyz (can be case insensitive)

upper(col2) = upper(col3)

XYZ, gxyzf (need to serach within a string )

upper(col2) like '%'||upper(col3)||'%'

XYZ, xyzjjjjjjjjj (need to serach within a string )

upper(col2) like upper(col3)||'%'

XYZ, gggggXYZ (need to serach within a string )

upper(col2) like '%'||upper(col3)

XYZ, Xavier Yellow Zebra (Match the full form of the abbreviation)

look up in the other table

Upvotes: 2

Related Questions