有没有办法在oracle sql中使用varchar2字符串的一部分作为主键？

I need to connect two tables by two columns.

我需要将两个表连接两列。

First table has primary key as integer type id.

第一个表的主键为整数类型id。

Second table has varchar2 type column which contains the same primary key but is located in the middle of a string.

第二个表具有varchar2类型列，该列包含相同的主键但位于字符串的中间。

For example in first table I have column called integer ID with ID like 1234. In the second table I have column with string like 'abcdefgh - 1234 (ijklmno)'.

例如，在第一个表中，我有一个名为整数ID的列，ID为1234.在第二个表中，我有一个字符串，如'abcdefgh - 1234（ijklmno）'。

Is there a way to use that nested key?

有没有办法使用该嵌套密钥？

3 个解决方案

#1

use regexp_replace. try this query

使用regexp_replace。试试这个查询

select * from table1
where table1.ID in (select regexp_replace(column2, '[A-Za-z]') from table2)

syntax of regexp_replace is
REGEXP_REPLACE(string, target [, replacement [, position [, occurrence [, regexp_modifiers]]]])

regexp_replace的语法是REGEXP_REPLACE（string，target [，replacement [，position [，occurrence [，regexp_modifiers]]]]）

#2

From Oracle 11g you can use a virtual column:

从Oracle 11g开始，您可以使用虚拟列：

SQL Fiddle

SQL小提琴

Oracle 11g R2 Schema Setup:

Oracle 11g R2架构设置：

CREATE TABLE TableA (
  id NUMBER(4,0) PRIMARY KEY
);

INSERT INTO TableA VALUES ( 1234 );

CREATE TABLE TableB (
  data VARCHAR2(25) NOT NULL
                    CHECK ( REGEXP_LIKE( data, '\w+ - (0|[1-9]\d{0,3}) \(\w+\)' ) ),
  id   NUMBER(4,0)  GENERATED ALWAYS AS ( TO_NUMBER( REGEXP_SUBSTR( data, '\w+ - (0|[1-9]\d{0,3}) \(\w+\)', 1, 1, null, 1 ) ) ) VIRTUAL,
  CONSTRAINT TableB__ID__FK FOREIGN KEY ( id ) REFERENCES TableA ( id )
);

INSERT INTO TableB ( data ) VALUES ( 'abcdefgh - 1234 (ijklmno)' );

Query 1:

查询1：

SELECT * FROM TableB

Results:

结果：

|                      DATA |   ID |
|---------------------------|------|
| abcdefgh - 1234 (ijklmno) | 1234 |

#3

Firstly, the table design is bad. You should have the primary key column with a foreign key relationship in the second table. And have a supporting index for the foreign key. Storing delimited text in a single column is not a good design and violates normalization.

首先，表设计很糟糕。您应该在第二个表中具有具有外键关系的主键列。并拥有外键的支持索引。在单个列中存储分隔文本不是一个好的设计，违反了规范化。

If the pattern of the string is same for all the rows in the second table, then you could use SUBSTR to extract the required string and then join with the first table.

如果第二个表中的所有行的字符串模式相同，则可以使用SUBSTR提取所需的字符串，然后与第一个表连接。

For example,

例如，

SQL> WITH t(str) AS(
  2  SELECT 'abcdefgh - 1234 (ijklmno)' FROM dual
  3  )
  4  SELECT substr(str, 12, 4) new_str FROM t;

NEW_
----
1234

SQL>

You could join substr(str, 12, 4) with the primary key of the first table,

你可以用第一个表的主键加入substr（str，12,4），

SELECT column_list
FROM table_1 t1
JOIN table_2 t2
ON (t1.primary_key = substr(column_name, 12, 4)
WHERE ...

From performance point of view, you need to create a function-based index on the column in second table:

从性能的角度来看，您需要在第二个表的列上创建基于函数的索引：

CREATE INDEX fn_idx_col ON table_2(substr(column_name, 12, 4));

From 11g onwards, VIRTUAL COLUMN is introduced. However, creating an index on the virtual column is same as creating a function-based index on a static column.

从11g起，推出了VIRTUAL COLUMN。但是，在虚拟列上创建索引与在静态列上创建基于函数的索引相同。

NOTE Since you do not have a foreign key relationship, you will always end up with tablescans on both the tables. If you have the proper relationships, then ideally Oracle would only use the primary key of the first table and avoid any table scan on the second table.

注意由于您没有外键关系，因此您将始终在两个表上使用表扫描。如果您有正确的关系，那么理想情况下Oracle只会使用第一个表的主键，并避免在第二个表上进行任何表扫描。

#1