CODEWITHSUNDEEP
sqloracle-databasesubstringtrim
Pretzel
Pretzel

Reputation: 8301

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.

Upvotes: 92

Views: 718968

Answers (8)

Amit Vashishtha
Amit Vashishtha

Reputation: 11

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

Upvotes: 1

vishal Pathak
vishal Pathak

Reputation: 1

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

Upvotes: 0

user1717270
user1717270

Reputation: 785

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.

Upvotes: 49

SJOH
SJOH

Reputation: 11

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

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332791

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.

Upvotes: 174

Loquillo Amigo
Loquillo Amigo

Reputation: 81

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

Upvotes: 8

EvilTeach
EvilTeach

Reputation: 28882

Another possibility would be the use of REGEXP_SUBSTR.

Upvotes: 2

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18818

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

Upvotes: 7

Related Questions