比较VARCHAR2和CHAR的最佳方法 [英] Best way to compare VARCHAR2 with CHAR

问题描述

我正在寻找将VARCHAR2(50 BYTE)与CHAR(12 BYTE)进行比较的最佳(​​最快)方法.

I'm searching for the best (and fastest) way to compare VARCHAR2(50 BYTE) with CHAR(12 BYTE).

有两个数据库，第一个包含带有CHAR列的table1(下划线表示填充CHAR长度的空格字符)

There are two databases, first contains a table1 with CHAR column (underline means space characters to fill CHAR length)

ID  VALUE
1   123-45___
2   123-456__
3   123-457__

第二个数据库(表2)包含不带空格的VARCHAR2.

second database (table2) contains VARCHAR2 without white space.

ID  VALUE
4   123-45
5   123-456
6   123-457

所以，我想要这样的东西

So, I want something like this

SELECT table1.ID FROM table1 WHERE table1.VALUE = '123-45'

推荐答案

在为table1.value列建立索引时，您不想为了进行比较而对其进行操作，因为那样会阻止使用索引.因此，您需要修改要查找的值:

As the table1.value column is indexed, you don't want to manipulate that for the comparison as that would prevent the index being used. So you'll need to modify the value you're looking up:

SELECT table1.ID FROM table1 WHERE table1.VALUE = RPAD('123-45', 12)

Oracle将对您显示的查询进行隐式操作，并且仍将使用索引.和要连接表的情况相同，但是在连接过程中是填充还是修剪取决于驱动程序是哪个表:

Oracle will do that implicitly with the query you showed though, and will still use the index. And the same if you're joining the tables, but whether you pad or trim during the join depends on which table is the driver:

SELECT table1.ID, table2.ID
FROM table1
JOIN table2 ON table2.value = RTRIM(table1.value)
WHERE table1.VALUE = RPAD('123-45', 12)

或者:

SELECT table1.ID
FROM table2
JOIN table1 ON table1.value = RPAD(table2.value, 12)

这篇关于比较VARCHAR2和CHAR的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！