86

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.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
Pretzel
  • 8,141
  • 16
  • 59
  • 84

8 Answers8

169

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.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Thanks. Very elegant! (good to know about REGEXP_SUBSTR, too.) I didn't even think to look for Regex support in Oracle. – Pretzel Dec 08 '10 at 16:26
  • In Oracle you can create functions (standalone or in a package) and use them in a select statement. – bart Dec 08 '10 at 18:02
  • 9
    Fails if run against values that do NOT contain the substring you're looking for. `instr` returns 0 if you have `INSTR('ABC/D', '_')`. In the end you have a substring from 0 to (0-1) which is null. Not good. – Marcel Stör Nov 06 '12 at 20:40
  • @MarcelStör you can do the trick with T.COLUMN || '_' so, there will always be an underscore in your string. – ThatsMe Jan 17 '22 at 07:28
46

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.

ann
  • 576
  • 1
  • 10
  • 19
user1717270
  • 755
  • 3
  • 16
  • 26
  • 1
    I'm upvoting this along the solution chosen by OP since it does a trick. Yet it's worth noting this solution is much slower than the @OMG Ponies's one, especially if used in where conditions. My tests have shown about 6 times slower performing of identical query. This question goes even further on the topic https://stackoverflow.com/questions/41156391/performance-and-readability-of-regexp-substr-vs-instr-and-substr – Ister Apr 05 '18 at 12:26
  • In my testing the ```INSTR``` solution performs pretty much equally as fast as the ```REGEXP``` solution. – alexherm Nov 21 '19 at 21:25
8
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

basgys
  • 4,320
  • 28
  • 39
7

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

Rajesh Chamarthi
  • 18,568
  • 4
  • 40
  • 67
2

Another possibility would be the use of REGEXP_SUBSTR.

EvilTeach
  • 28,120
  • 21
  • 85
  • 141
1

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
ZINE Mahmoud
  • 1,272
  • 1
  • 17
  • 32
0

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
Dhaval Simaria
  • 1,886
  • 3
  • 28
  • 36
SJOH
  • 11
0

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