Home » SQL & PL/SQL » SQL & PL/SQL » Help using regexp_substr - Urgent Help needed (Oracle 11)
Help using regexp_substr - Urgent Help needed [message #654525] Thu, 04 August 2016 22:18 Go to next message
Member2014
Messages: 33
Registered: July 2014
Location: USA
Member
I need to parse a string to get a part of it and am trying to use a regexp_substr

Sample string: AA^Col1^Col2^Col3^Col4^Col5

I need to get the output stripped of delimiter ^ and the last column Col5 and the output to be AA^Col1^Col2^Col3^Col4

Can someone please help me as to how this can be done. TIA.
Re: Help using regexp_substr - Urgent Help needed [message #654527 is a reply to message #654525] Thu, 04 August 2016 22:46 Go to previous messageGo to next message
Member2014
Messages: 33
Registered: July 2014
Location: USA
Member
I need to parse a string to get a part of it and am trying to use a regexp_substr

 Sample string: AA^Col1^Col2^Col3^Col4^Col5 

I need to get the output stripped of delimiter ^ and the last column Col5 and the output to be
AA^Col1^Col2^Col3^Col4 

Can someone please help me as to how this can be done. TIA.[/quote]
Re: Help using regexp_substr - Urgent Help needed [message #654528 is a reply to message #654525] Thu, 04 August 2016 22:47 Go to previous messageGo to next message
Member2014
Messages: 33
Registered: July 2014
Location: USA
Member
Thank you for the guidlines. I have modified the post and added the tags.
Re: Help using regexp_substr - Urgent Help needed [message #654529 is a reply to message #654528] Thu, 04 August 2016 22:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Forgive me.
What does this thread have to do with Oracle database?
I see nothing involving SQL or any DB in what you have posted.

Please explain why it is urgent for me to solve this issue for you?
Re: Help using regexp_substr - Urgent Help needed [message #654530 is a reply to message #654525] Thu, 04 August 2016 23:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> select regexp_substr ('AA^Col1^Col2^Col3^Col4^Col5', '([^\^]+\^){4}?[^\^]+') as output
  2  from   dual
  3  /

OUTPUT
----------------------
AA^Col1^Col2^Col3^Col4

1 row selected.

[Updated on: Thu, 04 August 2016 23:54]

Report message to a moderator

Re: Help using regexp_substr - Urgent Help needed [message #654532 is a reply to message #654530] Fri, 05 August 2016 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 67880
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As you want to remove the last item, REGEXP_REPLACE is a better option than REGEXP_SUBSTR:
SQL> select regexp_replace('AA^Col1^Col2^Col3^Col4^Col5','\^[^^]*$') from dual;
REGEXP_REPLACE('AA^COL
----------------------
AA^Col1^Col2^Col3^Col4
Re: Help using regexp_substr - Urgent Help needed [message #654534 is a reply to message #654525] Fri, 05 August 2016 01:24 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
You could do it with simple instr and substr:

with t as (select 'AA^Col1^Col2^Col3^Col4^Col5' val from dual)
select substr(val, 1, instr(val, '^', -1)-1)
from t
Re: Help using regexp_substr - Urgent Help needed [message #654552 is a reply to message #654529] Fri, 05 August 2016 08:39 Go to previous messageGo to next message
Member2014
Messages: 33
Registered: July 2014
Location: USA
Member
Thanks for looking. Since this is an oracle function I posted it here.
This function is new to me and was trying to learn the usage looking at some of the posts but was not able to derive what I wanted and I needed it to fix some issues.
Re: Help using regexp_substr - Urgent Help needed [message #654553 is a reply to message #654552] Fri, 05 August 2016 08:41 Go to previous message
Member2014
Messages: 33
Registered: July 2014
Location: USA
Member
Thank u Barbara Boehmer, Michel Cadot, pablolee. All the three approaches worked and I understand the function better no was well.
Appretiate the quick help.
Previous Topic: Regular expression search
Next Topic: Sum from part of coloumn
Goto Forum:
  


Current Time: Sun Jun 13 16:29:27 CDT 2021