您可以使用:
SELECT *
FROM TA
WHERE REGEXP_LIKE(inv_ref,'^(CT|MX)/32[7-9][0-9]/20$')
然后,如果您有测试数据:
CREATE TABLE TA ( inv_ref,is_valid ) AS
SELECT 'MX/3280/20','Valid' FROM DUAL UNION ALL
SELECT 'CT/3281/20','Valid' FROM DUAL UNION ALL
SELECT 'CT/3109/20','Invalid,number too low' FROM DUAL UNION ALL
SELECT 'MX/3272/20','Valid' FROM DUAL UNION ALL
SELECT 'RF/3275/20',wrong start' FROM DUAL UNION ALL
SELECT 'CX/3299/20',wrong start' FROM DUAL UNION ALL
SELECT 'MT/3270/20',wrong start' FROM DUAL UNION ALL
SELECT 'ACT/3270/20',wrong start' FROM DUAL;
这将输出:
INV_REF | IS_VALID
:--------- | :-------
MX/3280/20 | Valid
CT/3281/20 | Valid
MX/3272/20 | Valid
db 提琴here
,
添加虚拟生成的列会容易得多:
alter table your_table add (
P1 generated always as (regexp_substr(inv_ref,'^[^/]+')),P2 generated always as (to_number(regexp_substr(inv_ref,'/(\d+)/',1,null,1))),P3 generated always as (regexp_substr(inv_ref,'[^/]+$'))
);
在这种情况下,您可以在这些列上使用标准谓词。此外,您甚至可以在这些列上创建索引。
完整测试用例:
CREATE TABLE YOUR_TABLE ( inv_ref ) AS
SELECT 'MX/3280/20' FROM DUAL UNION ALL
SELECT 'CT/3281/20' FROM DUAL UNION ALL
SELECT 'CT/3109/20' FROM DUAL UNION ALL
SELECT 'MX/3272/20' FROM DUAL UNION ALL
SELECT 'RF/3275/20' FROM DUAL UNION ALL
SELECT 'CX/3299/20' FROM DUAL UNION ALL
SELECT 'MT/3270/20' FROM DUAL UNION ALL
SELECT 'ACT/3270/20' FROM DUAL;
alter table your_table add (
P1 generated always as (regexp_substr(inv_ref,'[^/]+$'))
);
select * from your_table
where p1 IN ('CT','MX')
and p2 BETWEEN 3270 and 3299;
结果:
MX/3280/20 MX 3280 20
CT/3281/20 CT 3281 20
MX/3272/20 MX 3272 20
,
您可以使用
SQL> create table my_test ( inv_ref varchar2(100) ) ;
SQL> insert into my_test values ( 'MX/3280/20') ;
SQL> insert into my_test values ( 'CD/3281/20') ;
SQL> insert into my_test values ( 'CD/3109/20') ;
SQL> insert into my_test values ( 'MX/3272/20') ;
SQL> insert into my_test values ( 'RF/3275/20') ;
Table created.
SQL> SQL> SQL>
1 row created.
SQL> SQL>
1 row created.
SQL> SQL>
1 row created.
SQL> SQL>
1 row created.
SQL> SQL>
1 row created.
SQL> commit ;
Commit complete.
两个选项(在我的情况下,我使用CD而不是CT)。 只要字符串仅限于示例,此选项将起作用。如果您有其他组合,则不会,因为CD | MX表示C或D或M或X )。查看答案的评论。 @MTO,谢谢您的评论。
SQL> select * from my_test where regexp_like(inv_ref,'[CD|MX]/32[7-9][0-9]/20')
INV_REF
--------------------------------------------------------------------------------
MX/3280/20
CD/3281/20
MX/3272/20
SQL> select * from my_test where regexp_like(inv_ref,'CD/32[7-9][0-9]/20') or
regexp_like(inv_ref,'MX/32[7-9][0-9]/20')
INV_REF
--------------------------------------------------------------------------------
MX/3280/20
CD/3281/20
MX/3272/20
,
您可以如下使用|
(或)分隔的值:
select * from TA where regexp_like(inv_ref,'^(CT|MX)/32[7-9][0-9]/20');
db<>fiddle
本文链接:https://www.f2er.com/1962923.html