如何在Oracle SQL中选择一个不超过特定字符的子字符串? [英] How to Select a substring in Oracle SQL up to a specific character?

问题描述

说我有一个表列，其结果如下:

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

SUBSTRING函数似乎不适合该任务，因为它基于位置并且下划线的位置不同.

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

我想到了TRIM功能(特别是RTRIM功能):

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

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.

推荐答案

使用SUBSTR，INSTR和NVL的组合(对于不带下划线的字符串)将返回您想要的内容:

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

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

结果:

output
------
ABC

使用:

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

参考:

• SUBSTR
• INSTR

Reference:

• SUBSTR
• INSTR

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

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

这篇关于如何在Oracle SQL中选择一个不超过特定字符的子字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！