[20260409]oracle regexp_like过滤不显示特定字符串.txt
[20260409]oracle regexp_like过滤不显示特定字符串.txt
--//假设要生成执行语句，排除minimum，maximum，lowval，hival这4个字符串如何实现，上网查询一下，可以使用否定前瞻，简单测试
--//看看,避免以后使用时再查资料。
1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.测试：
SCOTT@book01p> @ col_list sys.hist_head$ minimum|maximum|lowval|hival
SELECT
minimum
,maximum
,lowval
,hival
,minimum_enc
,maximum_enc
FROM sys.hist_head$
SCOTT@book01p> @ col_list sys.hist_head$ minimum$|maximum$|lowval|hival
SELECT
minimum
,maximum
,lowval
,hival
FROM sys.hist_head$
SCOTT@book01p> /
FROM sys.hist_head$
*
ERROR at line 6:
ORA-01031: insufficient privileges
--//19c开始查询sys.hist_head$，显示这4个字段会报ORA-01031: insufficient privileges错误。
--//显示4个字段的执行语句,注多显示了2个字段。如何写正则表达式实现排除这4个字段的情况呢？
--//使用否定前瞻,测试失败，不知道那里出了问题，使用kimi给出的结果测试不行：
## 使用否定前瞻(推荐)
```sql
SELECT * FROM your_table
WHERE REGEXP_LIKE(column_name, '^((?!.*(test|temp)).)*$', 'i');
```
## 更简洁的写法
```sql
-- 排除 test 或 temp 出现在任意位置
SELECT * FROM your_table
WHERE REGEXP_LIKE(column_name, '^((?!test)(?!temp).)*$', 'i');
```
--//两种方法都测试，发现都是不对，好像对方理解错误，指包含test以及temp的字符串,好像也不对。
SCOTT@book01p> @ col_list sys.hist_head$ ^((?!.*(minimum|maximum|lowval|hival)).)*$
SELECT
FROM sys.hist_head$
--//kimi还给出完整示例，测试根本没有输出：
## 完整示例
```sql
-- 测试数据
WITH demo AS (
SELECT 'hello world' AS col FROM dual UNION ALL
SELECT 'this is test data' FROM dual UNION ALL
SELECT 'temp folder here' FROM dual UNION ALL
SELECT 'TEST server' FROM dual UNION ALL -- 大写
SELECT 'temporary file' FROM dual UNION ALL -- 包含 temp
SELECT 'production env' FROM dual
)
-- 查询：排除包含 test 或 temp 的记录(不区分大小写)
SELECT * FROM demo
WHERE REGEXP_LIKE(col, '^((?!.*(test|temp)).)*$', 'i');
```
--//说明AI有时候并不靠谱。按照以前的理解，执行如下：
WITH demo AS (
SELECT 'hello world' AS col FROM dual UNION ALL
SELECT 'this is test data' FROM dual UNION ALL
SELECT 'temp folder here' FROM dual UNION ALL
SELECT 'TEST server' FROM dual UNION ALL -- 大写
SELECT 'temporary file' FROM dual UNION ALL -- 包含 temp
SELECT 'production env' FROM dual union all
SELECT 'test' FROM dual union all
SELECT 'test ' FROM dual union all
SELECT 'Temp' FROM dual
)
SELECT * FROM demo
WHERE REGEXP_LIKE(col,'[^test|temp]','i');
COL
-----------------
hello world
this is test data
temp folder here
TEST server
temporary file
production env
test
7 rows selected.
--//很明显不显示完整的test，temp行，注：test后面空格的输出。不过这个完全符合我的需求。
SCOTT@book01p> @ col_list sys.hist_head$ [^minimum|maximum|lowval|hival]
SELECT
obj#
,col#
,bucket_cnt
,row_cnt
,cache_cnt
,null_cnt
,timestamp#
,sample_size
,distcnt
,density
,intcol#
,spare1
,spare2
,avgcln
,spare3
,spare4
,minimum_enc
,maximum_enc
FROM sys.hist_head$
--//这样好理解为什么minimum_enc，maximum_enc会输出。按照完整行来匹配的。
--//当然还有一种选择使用not REGEXP_LIKE。我写的col_list脚本参数可以使用数字序列。
SCOTT@book01p> @ desc sys.hist_head$
Name Null? Type
----------- -------- ----------------------------
1 OBJ# NOT NULL NUMBER
2 COL# NOT NULL NUMBER
3 BUCKET_CNT NOT NULL NUMBER
4 ROW_CNT NOT NULL NUMBER
5 CACHE_CNT NUMBER
6 NULL_CNT NUMBER
7 TIMESTAMP# DATE
8 SAMPLE_SIZE NUMBER
9 MINIMUM NUMBER
10 MAXIMUM NUMBER
11 DISTCNT NUMBER
12 LOWVAL RAW(1000)
13 HIVAL RAW(1000)
14 DENSITY NUMBER
15 INTCOL# NOT NULL NUMBER
16 SPARE1 NUMBER
17 SPARE2 NUMBER
18 AVGCLN NUMBER
19 SPARE3 NUMBER
20 SPARE4 NUMBER
21 MINIMUM_ENC RAW(1000)
22 MAXIMUM_ENC RAW(1000)
SCOTT@book01p> @ numlist 1-8,11,14-22
NUM_LISTS
--------------------------------------------------------------------------------
1,2,3,4,5,6,7,8,11,14,15,16,17,18,19,20,21,22
SCOTT@book01p> @ col_list sys.hist_head$ 1,2,3,4,5,6,7,8,11,14,15,16,17,18,19,20,21,22
SELECT
obj#
,col#
,bucket_cnt
,row_cnt
,cache_cnt
,null_cnt
,timestamp#
,sample_size
,distcnt
,density
,intcol#
,spare1
,spare2
,avgcln
,spare3
,spare4
,minimum_enc
,maximum_enc
FROM sys.hist_head$
3.还是不知道如何实现排除特定字符串，比如前面例子如果不显示包含spare开头字段。
--//比如这样显示spare开头字符串。
SYS@book> @ col_list sys.hist_head$ spare.*$
SELECT
spare1
,spare2
,spare3
,spare4
FROM sys.hist_head$
--//而写成如下，竟然全部输出。
SYS@book> @ col_list sys.hist_head$ [^spare.*$]
SELECT
obj#
,col#
,bucket_cnt
,row_cnt
,cache_cnt
,null_cnt
,timestamp#
,sample_size
,minimum
,maximum
,distcnt
,lowval
,hival
,density
,intcol#
,spare1
,spare2
,avgcln
,spare3
,spare4
,minimum_enc
,maximum_enc
FROM sys.hist_head$
--//搞不懂写成如下，输出竟然是对的：
SYS@book> @ col_list sys.hist_head$ [^spare][^1]
SELECT
obj#
,col#
,bucket_cnt
,row_cnt
,cache_cnt
,null_cnt
,timestamp#
,sample_size
,minimum
,maximum
,distcnt
,lowval
,hival
,density
,intcol#
,avgcln
,minimum_enc
,maximum_enc
FROM sys.hist_head$
--//只能讲正则表达式可以很简单，复杂也很复杂，放弃探究。
4.附上测试使用脚本：
$ cat numlist.sql
/* Formatted on 2026-02-28 16:17:48 (QP5 v5.277) */
column num_lists format a80
WITH input_data AS (-- 输入字符串
SELECT '&1' AS input_str FROM DUAL)
,split_parts
AS ( -- 步骤1：按逗号拆分
SELECT REGEXP_SUBSTR (
input_str
,'[^,]+'
,1
,LEVEL)
AS part
FROM input_data
CONNECT BY REGEXP_SUBSTR (
input_str
,'[^,]+'
,1
,LEVEL)
IS NOT NULL)
,expanded
AS (-- 步骤2：处理每个部分(范围或单个数字)
SELECT CASE -- 如果是范围格式(如 2-5)
WHEN INSTR (part, '-') > 0 THEN TO_NUMBER (REGEXP_SUBSTR (part, '^[0-9]+')) -- 如果是单个数字
ELSE TO_NUMBER (part) END AS start_num
,CASE
WHEN INSTR (part, '-') > 0 THEN TO_NUMBER (REGEXP_SUBSTR (part, '[0-9]+$'))
ELSE TO_NUMBER (part)
END
AS end_num
FROM split_parts)
,-- 步骤3：使用递归生成序列
numbers (n, end_n)
AS (SELECT start_num, end_num FROM expanded
UNION ALL
SELECT n + 1, end_n
FROM numbers
WHERE n < end_n)
-- 最终结果
SELECT
-- distinct n AS result_number
LISTAGG (distinct n, ',') WITHIN GROUP (ORDER BY n) num_lists
FROM numbers
ORDER BY n;
$ cat col_list.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
--------------------------------------------------------------------------------
--
-- Name: col_list.sql
-- Purpose: display table of column_name list.
--
-- Author: lfree
-- Usage:
-- @col_list owner.table_name regexp_column_list|column_lists
-- column_list format : 1,2,4,5
--
--------------------------------------------------------------------------------
set term off head off feedback off
col 1 new_value 1
col 2 new_value 2
col tpt_comment1 new_value _tpt_comment1
col tpt_comment2 new_value _tpt_comment2
define arg1=&1
select null "2" from dual where 1=2;
select
decode('&2',null,'*',trim(',' from '&2')) "2"
,CASE WHEN INSTR (LOWER ('&2'), ',') > 0 THEN '--' WHEN '&2' IS NULL THEN '--' ELSE ' ' END tpt_comment1
,CASE WHEN INSTR (LOWER ('&2'), ',') > 0 THEN ' ' WHEN '&2' IS NULL THEN '--' ELSE '--' END tpt_comment2
,case when REGEXP_LIKE (UPPER ('&1'), '^GV_\$|^GV\$|^V_\$|^V\$|^DBA_|^USER_|^CDB_') THEN 'SYS.'||'&1' else '&1' END "1"
from dual;
set term on
def _sp_tmpfile=&_tpt_tempdir/sp_&_tpt_tempfile..tmp
spool &_sp_tmpfile
SELECT 'SELECT'
|| CHR (10)
|| ' '
|| LISTAGG (LOWER (column_name), CHR (10) || ',') WITHIN GROUP (ORDER BY column_id)
|| CHR (10)
|| 'FROM &&arg1'
c80
FROM ( SELECT data_type
,column_id
,column_name
,data_type
FROM dba_tab_cols
WHERE UPPER (table_name) LIKE
replace(
UPPER (
CASE WHEN INSTR ('&&1', '.') > 0 THEN SUBSTR ('&&1', INSTR ('&&1', '.') + 1) ELSE '&&1' END)
,'V$','V_$')
AND owner LIKE
CASE
WHEN INSTR ('&&1', '.') > 0 THEN UPPER (SUBSTR ('&&1', 1, INSTR ('&&1', '.') - 1))
ELSE USER
END ESCAPE '\'
AND HIDDEN_COLUMN = 'NO'
&_tpt_comment1 AND REGEXP_LIKE (LOWER (COLUMN_NAME), LOWER ('&&2'))
&_tpt_comment2 AND COLUMN_id in ( &&2)
ORDER BY column_id);
spool off
set head on feedback 6
get &_sp_tmpfile nolist
host &_delete &_sp_tmpfile
--//假设要生成执行语句，排除minimum，maximum，lowval，hival这4个字符串如何实现，上网查询一下，可以使用否定前瞻，简单测试
--//看看,避免以后使用时再查资料。
1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.测试：
SCOTT@book01p> @ col_list sys.hist_head$ minimum|maximum|lowval|hival
SELECT
minimum
,maximum
,lowval
,hival
,minimum_enc
,maximum_enc
FROM sys.hist_head$
SCOTT@book01p> @ col_list sys.hist_head$ minimum$|maximum$|lowval|hival
SELECT
minimum
,maximum
,lowval
,hival
FROM sys.hist_head$
SCOTT@book01p> /
FROM sys.hist_head$
*
ERROR at line 6:
ORA-01031: insufficient privileges
--//19c开始查询sys.hist_head$，显示这4个字段会报ORA-01031: insufficient privileges错误。
--//显示4个字段的执行语句,注多显示了2个字段。如何写正则表达式实现排除这4个字段的情况呢？
--//使用否定前瞻,测试失败，不知道那里出了问题，使用kimi给出的结果测试不行：
## 使用否定前瞻(推荐)
```sql
SELECT * FROM your_table
WHERE REGEXP_LIKE(column_name, '^((?!.*(test|temp)).)*$', 'i');
```
## 更简洁的写法
```sql
-- 排除 test 或 temp 出现在任意位置
SELECT * FROM your_table
WHERE REGEXP_LIKE(column_name, '^((?!test)(?!temp).)*$', 'i');
```
--//两种方法都测试，发现都是不对，好像对方理解错误，指包含test以及temp的字符串,好像也不对。
SCOTT@book01p> @ col_list sys.hist_head$ ^((?!.*(minimum|maximum|lowval|hival)).)*$
SELECT
FROM sys.hist_head$
--//kimi还给出完整示例，测试根本没有输出：
## 完整示例
```sql
-- 测试数据
WITH demo AS (
SELECT 'hello world' AS col FROM dual UNION ALL
SELECT 'this is test data' FROM dual UNION ALL
SELECT 'temp folder here' FROM dual UNION ALL
SELECT 'TEST server' FROM dual UNION ALL -- 大写
SELECT 'temporary file' FROM dual UNION ALL -- 包含 temp
SELECT 'production env' FROM dual
)
-- 查询：排除包含 test 或 temp 的记录(不区分大小写)
SELECT * FROM demo
WHERE REGEXP_LIKE(col, '^((?!.*(test|temp)).)*$', 'i');
```
--//说明AI有时候并不靠谱。按照以前的理解，执行如下：
WITH demo AS (
SELECT 'hello world' AS col FROM dual UNION ALL
SELECT 'this is test data' FROM dual UNION ALL
SELECT 'temp folder here' FROM dual UNION ALL
SELECT 'TEST server' FROM dual UNION ALL -- 大写
SELECT 'temporary file' FROM dual UNION ALL -- 包含 temp
SELECT 'production env' FROM dual union all
SELECT 'test' FROM dual union all
SELECT 'test ' FROM dual union all
SELECT 'Temp' FROM dual
)
SELECT * FROM demo
WHERE REGEXP_LIKE(col,'[^test|temp]','i');
COL
-----------------
hello world
this is test data
temp folder here
TEST server
temporary file
production env
test
7 rows selected.
--//很明显不显示完整的test，temp行，注：test后面空格的输出。不过这个完全符合我的需求。
SCOTT@book01p> @ col_list sys.hist_head$ [^minimum|maximum|lowval|hival]
SELECT
obj#
,col#
,bucket_cnt
,row_cnt
,cache_cnt
,null_cnt
,timestamp#
,sample_size
,distcnt
,density
,intcol#
,spare1
,spare2
,avgcln
,spare3
,spare4
,minimum_enc
,maximum_enc
FROM sys.hist_head$
--//这样好理解为什么minimum_enc，maximum_enc会输出。按照完整行来匹配的。
--//当然还有一种选择使用not REGEXP_LIKE。我写的col_list脚本参数可以使用数字序列。
SCOTT@book01p> @ desc sys.hist_head$
Name Null? Type
----------- -------- ----------------------------
1 OBJ# NOT NULL NUMBER
2 COL# NOT NULL NUMBER
3 BUCKET_CNT NOT NULL NUMBER
4 ROW_CNT NOT NULL NUMBER
5 CACHE_CNT NUMBER
6 NULL_CNT NUMBER
7 TIMESTAMP# DATE
8 SAMPLE_SIZE NUMBER
9 MINIMUM NUMBER
10 MAXIMUM NUMBER
11 DISTCNT NUMBER
12 LOWVAL RAW(1000)
13 HIVAL RAW(1000)
14 DENSITY NUMBER
15 INTCOL# NOT NULL NUMBER
16 SPARE1 NUMBER
17 SPARE2 NUMBER
18 AVGCLN NUMBER
19 SPARE3 NUMBER
20 SPARE4 NUMBER
21 MINIMUM_ENC RAW(1000)
22 MAXIMUM_ENC RAW(1000)
SCOTT@book01p> @ numlist 1-8,11,14-22
NUM_LISTS
--------------------------------------------------------------------------------
1,2,3,4,5,6,7,8,11,14,15,16,17,18,19,20,21,22
SCOTT@book01p> @ col_list sys.hist_head$ 1,2,3,4,5,6,7,8,11,14,15,16,17,18,19,20,21,22
SELECT
obj#
,col#
,bucket_cnt
,row_cnt
,cache_cnt
,null_cnt
,timestamp#
,sample_size
,distcnt
,density
,intcol#
,spare1
,spare2
,avgcln
,spare3
,spare4
,minimum_enc
,maximum_enc
FROM sys.hist_head$
3.还是不知道如何实现排除特定字符串，比如前面例子如果不显示包含spare开头字段。
--//比如这样显示spare开头字符串。
SYS@book> @ col_list sys.hist_head$ spare.*$
SELECT
spare1
,spare2
,spare3
,spare4
FROM sys.hist_head$
--//而写成如下，竟然全部输出。
SYS@book> @ col_list sys.hist_head$ [^spare.*$]
SELECT
obj#
,col#
,bucket_cnt
,row_cnt
,cache_cnt
,null_cnt
,timestamp#
,sample_size
,minimum
,maximum
,distcnt
,lowval
,hival
,density
,intcol#
,spare1
,spare2
,avgcln
,spare3
,spare4
,minimum_enc
,maximum_enc
FROM sys.hist_head$
--//搞不懂写成如下，输出竟然是对的：
SYS@book> @ col_list sys.hist_head$ [^spare][^1]
SELECT
obj#
,col#
,bucket_cnt
,row_cnt
,cache_cnt
,null_cnt
,timestamp#
,sample_size
,minimum
,maximum
,distcnt
,lowval
,hival
,density
,intcol#
,avgcln
,minimum_enc
,maximum_enc
FROM sys.hist_head$
--//只能讲正则表达式可以很简单，复杂也很复杂，放弃探究。
4.附上测试使用脚本：
$ cat numlist.sql
/* Formatted on 2026-02-28 16:17:48 (QP5 v5.277) */
column num_lists format a80
WITH input_data AS (-- 输入字符串
SELECT '&1' AS input_str FROM DUAL)
,split_parts
AS ( -- 步骤1：按逗号拆分
SELECT REGEXP_SUBSTR (
input_str
,'[^,]+'
,1
,LEVEL)
AS part
FROM input_data
CONNECT BY REGEXP_SUBSTR (
input_str
,'[^,]+'
,1
,LEVEL)
IS NOT NULL)
,expanded
AS (-- 步骤2：处理每个部分(范围或单个数字)
SELECT CASE -- 如果是范围格式(如 2-5)
WHEN INSTR (part, '-') > 0 THEN TO_NUMBER (REGEXP_SUBSTR (part, '^[0-9]+')) -- 如果是单个数字
ELSE TO_NUMBER (part) END AS start_num
,CASE
WHEN INSTR (part, '-') > 0 THEN TO_NUMBER (REGEXP_SUBSTR (part, '[0-9]+$'))
ELSE TO_NUMBER (part)
END
AS end_num
FROM split_parts)
,-- 步骤3：使用递归生成序列
numbers (n, end_n)
AS (SELECT start_num, end_num FROM expanded
UNION ALL
SELECT n + 1, end_n
FROM numbers
WHERE n < end_n)
-- 最终结果
SELECT
-- distinct n AS result_number
LISTAGG (distinct n, ',') WITHIN GROUP (ORDER BY n) num_lists
FROM numbers
ORDER BY n;
$ cat col_list.sql
-- Copyright 2023 lfree. All rights reserved.
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions.
--------------------------------------------------------------------------------
--
-- Name: col_list.sql
-- Purpose: display table of column_name list.
--
-- Author: lfree
-- Usage:
-- @col_list owner.table_name regexp_column_list|column_lists
-- column_list format : 1,2,4,5
--
--------------------------------------------------------------------------------
set term off head off feedback off
col 1 new_value 1
col 2 new_value 2
col tpt_comment1 new_value _tpt_comment1
col tpt_comment2 new_value _tpt_comment2
define arg1=&1
select null "2" from dual where 1=2;
select
decode('&2',null,'*',trim(',' from '&2')) "2"
,CASE WHEN INSTR (LOWER ('&2'), ',') > 0 THEN '--' WHEN '&2' IS NULL THEN '--' ELSE ' ' END tpt_comment1
,CASE WHEN INSTR (LOWER ('&2'), ',') > 0 THEN ' ' WHEN '&2' IS NULL THEN '--' ELSE '--' END tpt_comment2
,case when REGEXP_LIKE (UPPER ('&1'), '^GV_\$|^GV\$|^V_\$|^V\$|^DBA_|^USER_|^CDB_') THEN 'SYS.'||'&1' else '&1' END "1"
from dual;
set term on
def _sp_tmpfile=&_tpt_tempdir/sp_&_tpt_tempfile..tmp
spool &_sp_tmpfile
SELECT 'SELECT'
|| CHR (10)
|| ' '
|| LISTAGG (LOWER (column_name), CHR (10) || ',') WITHIN GROUP (ORDER BY column_id)
|| CHR (10)
|| 'FROM &&arg1'
c80
FROM ( SELECT data_type
,column_id
,column_name
,data_type
FROM dba_tab_cols
WHERE UPPER (table_name) LIKE
replace(
UPPER (
CASE WHEN INSTR ('&&1', '.') > 0 THEN SUBSTR ('&&1', INSTR ('&&1', '.') + 1) ELSE '&&1' END)
,'V$','V_$')
AND owner LIKE
CASE
WHEN INSTR ('&&1', '.') > 0 THEN UPPER (SUBSTR ('&&1', 1, INSTR ('&&1', '.') - 1))
ELSE USER
END ESCAPE '\'
AND HIDDEN_COLUMN = 'NO'
&_tpt_comment1 AND REGEXP_LIKE (LOWER (COLUMN_NAME), LOWER ('&&2'))
&_tpt_comment2 AND COLUMN_id in ( &&2)
ORDER BY column_id);
spool off
set head on feedback 6
get &_sp_tmpfile nolist
host &_delete &_sp_tmpfile
浙公网安备 33010602011771号