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

Question

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.

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.

Answer 1

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:

• SUBSTR
• INSTR

Addendum

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

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

Another possibility would be the use of REGEXP_SUBSTR.

Answer 6

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