问题描述
假设我有一个表格列，其结果如下:
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
如果使用 Oracle10g+，您可以通过 REGEXP_SUBSTR 使用正则表达式.
If using Oracle10g+, you can use regex via REGEXP_SUBSTR.
这篇关于如何在 Oracle SQL 中选择特定字符的子字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！