如何在 OracleSQL 中选择一个特定字符的子字符串？

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

ABC_blahblahblah
DEFGH_moreblahblahblah
IJKLMNOP_moremoremoremore

我希望能够编写一个查询，从所述表中选择该列，但只返回直到 Underscore (_)字符的子字符串。例如:

ABC
DEFGH
IJKLMNOP

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

我考虑了 TRIM 函数(特别是 RTRIM 函数) :

SELECT RTRIM('listofchars' FROM somecolumn)
FROM sometable

但是我不确定如何使用它，因为它似乎只是删除某个特定的列表/字符集，而且我实际上只是在下划线字符之前的字符之后。

632616 次浏览
小开

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.

  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

小开
最佳答案

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

Result:

output
------
ABC

Use:

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

Reference:

Addendum

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

小开

Another possibility would be the use of REGEXP_SUBSTR.

小开

This can be done using REGEXP_SUBSTR easily.

Please use 

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

where STRING_EXAMPLE is your string.

Try:

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

It will solve your problem.

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

is the right answer, as posted by 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 INSTR it will give you a NULL because it did not find any ".", i.e. it will print from 0 to 0. With REGEXP_SUBSTR you will get the right answer in all cases:

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

HOST

and

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

HOST

小开

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
小开

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_value we can do as:

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

You will find result: Ple

小开

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

Table      Structure
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"}

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

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                                                        cut_str
-----------------------------------------------------------   ----------
{"clientId":"con-bjp","clientName":"ABC","providerId":"SBS"}    con-bjp
{"IdType":"AccountNo","Id":"XXXXXXXX3521","ToPricingId":"XXXXXXXX3521","clientId":"Test-Cust","clientName":"MFX"}   Test-Cust

提交答案