SQL 比较oracle中的列字符串值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13771336/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Comparing column string values in oracle
提问by Stu
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 Col3do not match Col2.
I am using Oracle 10g.
现在我需要找到其中的值Col3不匹配的所有行Col2。我正在使用 Oracle 10g。
回答by Randy
XYZ, XYZ (word to word match)
XYZ、XYZ（字对字匹配）
col2 = col3
XYZ, xyz (can be case insensitive)
XYZ, xyz（可以不区分大小写）
upper(col2) = upper(col3)
XYZ, gxyzf (need to serach within a string )
XYZ, gxyzf（需要在一个字符串中搜索）
upper(col2) like '%'||upper(col3)||'%'
XYZ, xyzjjjjjjjjj (need to serach within a string )
XYZ, xyzjjjjjjjjj（需要在一个字符串中搜索）
upper(col2) like upper(col3)||'%'
XYZ, gggggXYZ (need to serach within a string )
XYZ, gggggXYZ（需要在字符串中搜索）
upper(col2) like '%'||upper(col3)
XYZ, Xavier Yellow Zebra (Match the full form of the abbreviation)
XYZ，Xavier Yellow Zebra（匹配缩写的完整形式）
look up in the other table
在另一张桌子上查找
回答by Fls'Zen
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.
也许这样的事情对你有用。where 子句有两个条件，只需要满足其中一个条件。当在 Col2 中找到 Col3 值时满足第一个条件，忽略大小写。当 Col2 是 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.
如果需要，您可以按照第一个条件的工作方式更改第二个条件，以允许 Col2 包含扩展名而不是完全扩展名。
回答by MT0
Oracle 11g R2 Schema Setup:
Oracle 11g R2 架构设置：
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:
查询 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 || '%'
)
结果：
| 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 |
