Comparing column string values in oracle

Question

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.

Why not use regex then, given Oracle supports them. What if you have XYZ, XavierX Yellowy Zebraz ? Can you please show us your table data, otherwise we will continue to shoot in the dark :D — bonCodigo
– bonCodigo, Commented Dec 7, 2012 at 21:43

Randy · Accepted Answer · 2012-12-07 21:44:37Z

2

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

answered Dec 7, 2012 at 21:44

Randy

16.7k2 gold badges40 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fls'Zen · Accepted Answer · 2012-12-07 21:46:52Z

0

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.

answered Dec 7, 2012 at 21:46

Fls'Zen

4,6641 gold badge31 silver badges37 bronze badges

1 Comment

Fls'Zen Over a year ago

Hmm, it's difficult to know what would be causing no rows. Try removing the WHERE clauses and doing a "SELECT * FROM Tbl1 LEFT JOIN Abbr ON Col3=Abbr.Key". That may give you some clues as to what is going on. If that returns rows, then introduce the WHERE clauses one at a time.

MT0 · Accepted Answer · 2015-11-06 00:57:29Z

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 |

Collectives™ on Stack Overflow

Comparing column string values in oracle

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related