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

Question

Let's say I have a table column that has results such as:

ABC_blahblahblah DEFGH_moreblahblahblah IJKLMNOP_moremoremoremore 

I would like to write a query that selects this column from the specified table, but returns only the substring to the Underscore (_) character. For example:

 ABC DEFGH IJKLMNOP 

The SUBSTRING function does not seem to fit the task because it is positioned and the underline position changes.

I was thinking about the TRIM function (RTRIM function):

 SELECT RTRIM('listofchars' FROM somecolumn) FROM sometable 

But I'm not sure how I could make this work, since it only seems to delete a specific list / character set, and I really only after the characters leading to the Underscore character.

Answer 1

Using a combination of SUBSTR, INSTR and NVL (for lines without underscores) will return what you want:

 SELECT NVL(SUBSTR('ABC_blah', 0, INSTR('ABC_blah', '_')-1), 'ABC_blah') AS output FROM DUAL 

Result:

 output ------ ABC 

Using:

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

Reference:

• SUBSTR
• INSTR

Adding

If you are using Oracle10g +, you can use regex through REGEXP_SUBSTR .

Answer 2

This can be done using REGEXP_SUBSTR.

Please, use

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

where STRING_EXAMPLE is your string.

Try:

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

This will solve your problem.

Answer 3

You need to get the position of the first underscore (using INSTR), and then get the portion of the line 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 

- the correct answer as published by user1717270

If you use INSTR , it will give you a position for the line, which assumes that it contains "_" in it. What if this is not so? Well, the answer will be 0. Therefore, when you want to print a line, it will print a NULL . Example. If you want to remove the domain from "host.domain". In some cases, you will only have a short name, that is, "host". Most likely you want to print "host". Well, with INSTR it will give you NULL , because it did not find any ".", That is, it will print from 0 to 0. With REGEXP_SUBSTR you will get the correct 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 might be to use REGEXP_SUBSTR.

Answer 6

Keep this in mind if all your rows in the column do not have underscores (... or otherwise, if the output is zero):

 SELECT COALESCE (SUBSTR("STRING_COLUMN" , 0, INSTR("STRING_COLUMN", '_')-1), "STRING_COLUMN") AS OUTPUT FROM DUAL 

Answer 7

To find any substring from a large string:

 string_value:=('This is String,Please search string 'Ple'); 

Then, to find the string 'Ple' from String_value , we can do this:

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

You will find the result: Ple