[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

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.

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

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

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

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.

Another possibility would be the use of REGEXP_SUBSTR.

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

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