如何在 Oracle SQL 中选择特定字符的子字符串？

How to Select a substring in Oracle SQL up to a specific character?

提问by Pretzel

Say I have a table column that has results like:

假设我有一个表列，其结果如下：

ABC_blahblahblah
DEFGH_moreblahblahblah
IJKLMNOP_moremoremoremore

I would like to be able to write a query that selects this column from said table, but only returns the substring up to the Underscore (_) character. For example:

我希望能够编写一个从所述表中选择此列的查询，但只返回下划线 (_) 字符之前的子字符串。例如：

ABC
DEFGH
IJKLMNOP

The SUBSTRING function doesn't seem to be up to the task because it is position-based and the position of the underscore varies.

SUBSTRING 函数似乎不能胜任这项任务，因为它是基于位置的，并且下划线的位置各不相同。

I thought about the TRIM function (the RTRIM function specifically):

我想到了 TRIM 函数（特别是 RTRIM 函数）：

SELECT RTRIM('listofchars' FROM somecolumn) 
FROM sometable

But I'm not sure how I'd get this to work since it only seems to remove a certain list/set of characters and I'm really only after the characters leading up to the Underscore character.

但我不确定如何让它工作，因为它似乎只删除了某个列表/字符集，而我实际上只是在字符导致下划线字符之后。

回答by OMG Ponies

Using a combination of SUBSTR, INSTR, and NVL (for strings without an underscore) will return what you want:

使用 SUBSTR、INSTR 和 NVL 的组合（对于没有下划线的字符串）将返回您想要的内容：

SELECT NVL(SUBSTR('ABC_blah', 0, INSTR('ABC_blah', '_')-1), 'ABC_blah') AS output
  FROM DUAL

Result:

结果：

output
------
ABC

Use:

用：

SELECT NVL(SUBSTR(t.column, 0, INSTR(t.column, '_')-1), t.column) AS output
  FROM YOUR_TABLE t

Reference:

参考：

• SUBSTR
• INSTR

• 子字符串
• 指令

Addendum

附录

If using Oracle10g+, you can use regex via REGEXP_SUBSTR.

如果使用 Oracle10g+，您可以通过REGEXP_SUBSTR使用正则表达式。

回答by user1717270

This can be done using REGEXP_SUBSTReasily.

这可以使用REGEXP_SUBSTR轻松完成。

Please use

请用

REGEXP_SUBSTR('STRING_EXAMPLE','[^_]+',1,1) 

where STRING_EXAMPLEis your string.

其中STRING_EXAMPLE是您的字符串。

Try:

尝试：

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

It will solve your problem.

它会解决你的问题。

回答by Rajesh Chamarthi

You need to get the position of the first underscore (using INSTR) and then get the part of the string from 1st charecter to (pos-1) using substr.

您需要获取第一个下划线的位置（使用 INSTR），然后使用 substr 获取从第一个字符到 (pos-1) 的字符串部分。

  1  select 'ABC_blahblahblah' test_string,
  2         instr('ABC_blahblahblah','_',1,1) position_underscore,
  3         substr('ABC_blahblahblah',1,instr('ABC_blahblahblah','_',1,1)-1) result
  4*   from dual
SQL> /

TEST_STRING      POSITION_UNDERSCORE RES
---------------- ------------------  ---
ABC_blahblahblah                  4  ABC

Instr documentation

说明文档

Susbtr Documentation

副文档

回答by Loquillo Amigo

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

is the right answer, as posted by user1717270

是正确的答案，由 user1717270 发布

If you use INSTR, it will give you the position for a string that assumes it contains "_" in it. What if it doesn't? Well the answer will be 0. Therefore, when you want to print the string, it will print a NULL. Example: If you want to remove the domain from a "host.domain". In some cases you will only have the short name, i.e. "host". Most likely you would like to print "host". Well, with INSTRit will give you a NULLbecause it did not find any ".", i.e. it will print from 0 to 0. With REGEXP_SUBSTRyou will get the right answer in all cases:

如果您使用INSTR，它将为您提供假定其中包含“_”的字符串的位置。如果没有呢？那么答案将是 0。因此，当你想打印字符串时，它会打印一个NULL. 示例：如果您想从“host.domain”中删除域。在某些情况下，您将只有短名称，即“主机”。很可能您想打印“主机”。好吧，INSTR它会给你一个，NULL因为它没有找到任何“.”，即它会从 0 打印到 0。REGEXP_SUBSTR在所有情况下你都会得到正确的答案：

SELECT REGEXP_SUBSTR('HOST.DOMAIN','[^.]+',1,1)  from dual;

HOST

主持人

and

和

SELECT REGEXP_SUBSTR('HOST','[^.]+',1,1)  from dual;

HOST

主持人

回答by EvilTeach

Another possibility would be the use of REGEXP_SUBSTR.

另一种可能性是使用REGEXP_SUBSTR。

回答by SJOH

Remember this if all your Strings in the column do not have an underscore (...or else if null value will be the output):

如果列中的所有字符串都没有下划线（...否则如果输出为空值），请记住这一点：

SELECT COALESCE
(SUBSTR("STRING_COLUMN" , 0, INSTR("STRING_COLUMN", '_')-1), 
"STRING_COLUMN") 
AS OUTPUT FROM DUAL

回答by vishal Pathak

To find any sub-string from large string:

要从大字符串中查找任何子字符串：

string_value:=('This is String,Please search string 'Ple');

Then to find the string 'Ple'from String_valuewe can do as:

然后'Ple'从String_value我们可以找到字符串：

select substr(string_value,instr(string_value,'Ple'),length('Ple')) from dual;

You will find result: Ple

你会发现结果： Ple

回答by Amit Vashishtha

In case if String position is not fixed then by below Select statement we can get the expected output.

如果字符串位置不固定，那么通过下面的 Select 语句，我们可以获得预期的输出。

Table Structure ID VARCHAR2(100 BYTE) CLIENT VARCHAR2(4000 BYTE)

表结构 ID VARCHAR2(100 BYTE) CLIENT VARCHAR2(4000 BYTE)

Data- ID CLIENT
1001 {"clientId":"con-bjp","clientName":"ABC","providerId":"SBS"}
1002 {"IdType":"AccountNo","Id":"XXXXXXXX3521","ToPricingId":"XXXXXXXX3521","clientId":"Test-Cust","clientName":"MFX"}

数据 ID 客户端
1001 {"clientId":"con-bjp","clientName":"ABC","providerId":"SBS"}
1002 {"IdType":"AccountNo","Id":"XXXXXXXX3521", "ToPricingId":"XXXXXXXX3521","clientId":"Test-Cust","clientName":"MFX"}

Requirement - Search "ClientId" string in CLIENT column and return the corresponding value. Like From "clientId":"con-bjp" --> con-bjp(Expected output)

要求 - 在 CLIENT 列中搜索“ClientId”字符串并返回相应的值。Like From "clientId":"con-bjp" --> con-bjp(预期输出)

select CLIENT,substr(substr(CLIENT,instr(CLIENT,'"clientId":"')+length('"clientId":"')),1,instr(substr(CLIENT,instr(CLIENT,'"clientId":"')+length('"clientId":"')),'"',1 )-1) cut_str from TEST_SC;

选择 CLIENT,substr(substr(CLIENT,instr(CLIENT,'"clientId":"')+length('"clientId":"')),1,instr(substr(CLIENT,instr(CLIENT,'"clientId") :"')+length('"clientId":"')),'"',1 )-1) cut_str 来自 TEST_SC;

CLIENT cut_str ----------------------------------------------------------- ---------- {"clientId":"con-bjp","clientName":"ABC","providerId":"SBS"} con-bjp {"IdType":"AccountNo","Id":"XXXXXXXX3521","ToPricingId":"XXXXXXXX3521","clientId":"Test-Cust","clientName":"MFX"} Test-Cust

客户端 cut_str ------------------------------------------------ ----------- ---------- {"clientId":"con-bjp","clientName":"ABC","providerId":"SBS"} con- bjp {"IdType":"AccountNo","Id":"XXXXXXXX3521","ToPricingId":"XXXXXXXX3521","clientId":"Test-Cust","clientName":"MFX"} Test-Cust