基本上,我在具有特殊字符的字段中有一些记录,通常如下所示:
id Content
1 1|1232
2 23|12323
3 33|233223
我想写一个查询,选择管道右侧的数字"|">
因此结果应该如下:查询的结果
1 = 1232
2 = 12323
外部。。。
您可以使用简单的字符串函数:
SELECT id,
SUBSTR( content, 1, INSTR( content, '|' ) - 1 ) AS before_pipe,
SUBSTR( content, INSTR( content, '|' ) + 1 ) AS after_pipe
FROM table_name
或者,使用正则表达式:
SELECT id,
REGEXP_SUBSTR( content, '^d+' ) AS before_pipe,
REGEXP_SUBSTR( content, 'd+$' ) AS after_pipe
FROM table_name
对于样本数据:
CREATE TABLE table_name ( id, content ) AS
SELECT 1, '1|1232' FROM DUAL UNION ALL
SELECT 2, '23|12323' FROM DUAL UNION ALL
SELECT 3, '33|233223' FROM DUAL;
两种输出:
ID BEFORE_PIE AFTER_PIPE 1 1 21232 2 23 1223 3 33 233223 根据示例数据,看看这两个选项(substr+instr和正则表达式(中的任何一个是否有帮助。第1-5行中的示例数据,查询从第6行开始。
SQL> with test (id, content) as 2 (select 1, '1|1232' from dual union all 3 select 2, '23|12323' from dual union all 4 select 3, '33|233223' from dual 5 ) 6 select id, 7 content, 8 -- 9 substr(content, instr(content, '|') + 1) result_1, 10 regexp_substr(content, 'd+$') result_2 11 from test; ID CONTENT RESULT_1 RESULT_2 ---------- --------- --------------- --------------- 1 1|1232 1232 1232 2 23|12323 12323 12323 3 33|233223 233223 233223 SQL>