将REGEXP_SUBSTR与Strings Qualifier一起使用 [英] Using REGEXP_SUBSTR with Strings Qualifier

问题描述

从类似的Stack Overflow线程获取示例， 在PL/SQL中删除特定字符之后的所有字符 和 如何在Oracle SQL最多包含一个特定字符?

Getting Examples from similar Stack Overflow threads, Remove all characters after a specific character in PL/SQL and How to Select a substring in Oracle SQL up to a specific character?

我只想检索出现字符串之前的前几个字符.

I would want to retrieve only the first characters before the occurrence of a string.

示例:

STRING_EXAMPLE
TREE_OF_APPLES

结果数据集应仅显示STRING_EXAM和TREE_OF_AP，因为PLE是我的定界符

The Resulting Data set should only show only STRING_EXAM and TREE_OF_AP because PLE is my delimiter

每当我使用下面的REGEXP_SUBSTR时，它只会得到STRING_，因为REGEXP_SUBSTR将PLE视为单独的表达式(P, L and E)，而不是单个表达式(PLE).

Whenever i use the below REGEXP_SUBSTR, It gets only STRING_ because REGEXP_SUBSTR treats PLE as separate expressions (P, L and E), not as a single expression (PLE).

SELECT REGEXP_SUBSTR('STRING_EXAMPLE','[^PLE]+',1,1) from dual;

如何在不使用大量INSTR和SUBSTR的情况下做到这一点?

How can i do this without using numerous INSTRs and SUBSTRs?

谢谢.

推荐答案

查询的问题是，如果使用[^PLE]，它将匹配除P或L或E以外的任何字符. PLE连续.因此，使用

The problem with your query is that if you use [^PLE] it would match any characters other than P or L or E. You are looking for an occurence of PLE consecutively. So, use

select REGEXP_SUBSTR(colname,'(.+)PLE',1,1,null,1) 
from tablename

这将返回直到字符串中最后一次出现PLE的子字符串.

This returns the substring up to the last occurrence of PLE in the string.

如果该字符串包含PLE的多个实例，并且仅需要提取直到第一次出现的子字符串，请使用

If the string contains multiple instances of PLE and only the substring up to the first occurrence needs to be extracted, use

select REGEXP_SUBSTR(colname,'(.+?)PLE',1,1,null,1) 
from tablename

这篇关于将REGEXP_SUBSTR与Strings Qualifier一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！