OraFAQ Forum: SQL & PL/SQL » Extract string between 2 text

Home » SQL & PL/SQL » SQL & PL/SQL » Extract string between 2 text (11gr2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Extract string between 2 text [message #684912]

Sun, 26 September 2021 05:47

guddu_12
Messages: 223
Registered: April 2012
Location: UK

Senior Member

Dear Sir,

I have below example of data to extract part of the string.

1> twin@@tea@@swirl@@coffee
2 inf/pre-part/sig/maint

My requirment is that if i pass "@@" at position 2 then output should be "swirl" and if i pass "/" and position 1 then output should be "pre-part"

Please help me find the solution
Input split string output
twin@@tea@@swirl@@coffee @@ swirl
twin@@tea@@swirl@@coffee / pre-part

Report message to a moderator

Re: Extract string between 2 text [message #684913 is a reply to message #684912]

Sun, 26 September 2021 06:09

Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Thu, 30 May 2019 12:32

Michel Cadot wrote on Thu, 28 February 2019 17:43

Why didn't you feedback and thank people who spent time to help you in your previous topics?
Your answer:

guddu_12 wrote on Fri, 01 March 2019 10:59
Hello Michel ,

I am not so bad to appreceiate for the help, I didn't check post as I was away form office. I will try the link give above and post you the finding
You did not.

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

[Updated on: Sun, 26 September 2021 06:09]

Report message to a moderator

Re: Extract string between 2 text [message #684914 is a reply to message #684913]

Sun, 26 September 2021 06:58

guddu_12
Messages: 223
Registered: April 2012
Location: UK

Senior Member

I appologies for my ill behaviour Michael

Input data set

    1> twin@@tea@@swirl@@coffee
    2> inf/pre-part/sig/maint

Expected Output

Input 	split                 string 	output
twin@@tea@@swirl@@coffee  	@@	swirl
twin@@tea@@swirl@@coffee	 / 	pre-part

Report message to a moderator

Re: Extract string between 2 text [message #684915 is a reply to message #684914]

Sun, 26 September 2021 07:25

Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA

Senior Member

Just use REGEXP_SUBSTR(string,'[^delimiter]+',1,position + 1):

SQL> select  regexp_substr('twin@@tea@@swirl@@coffee','[^@]+',1,2+1)
  2    from  dual
  3  /

REGEX
-----
swirl

SQL> select  regexp_substr('inf/pre-part/sig/maint','[^/]+',1,1+1)
  2    from  dual
  3  /

REGEXP_S
--------
pre-part

SQL>

SY.

[Updated on: Sun, 26 September 2021 07:26]

Report message to a moderator

Re: Extract string between 2 text [message #684916 is a reply to message #684915]

Sun, 26 September 2021 07:46

guddu_12
Messages: 223
Registered: April 2012
Location: UK

Senior Member

Hi Thanks for the quick reply

the pattern string will vary that is fine but the postion will also vary and i wanted to see for given pattern when postion is supplied it shold return. Please see the example below

input -> ('one***two***three')   searche_pattern -> '***'    desire_Postion -> 2    returns ->'two'.

input -> ('abc@@def@@ghi')       searche_pattern -> '@@'    desire_Postion -> 3 returns 'ghi'.

input ->  ('abc@@def@@ghi)       searche_pattern -> '@@'   desire_Postion -> -3  returns 'abc'

last one search in reverse but i can pass desire_position 1 to get that value

Report message to a moderator

Re: Extract string between 2 text [message #684917 is a reply to message #684916]

Sun, 26 September 2021 09:43

Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA

Senior Member

You keep changing requirements. It was position + 1 now it is position and now position can be negative. Then use SUBSTR/INSTR:

with sample as (
                select 'one***two***three' str,'***' delimiter,2 position from dual union all
                select 'abc@@def@@ghi' str,'@@' delimiter,3 position from dual union all
                select 'abc@@def@@ghi' str,'@@' delimiter,-3 position from dual
               )
select  sample.*,
        substr(
               str,
               instr(delimiter || str,delimiter,sign(position),abs(position)),
               instr(str || delimiter,delimiter,sign(position),abs(position)) - instr(delimiter || str,delimiter,sign(position),abs(position))
              ) sub_str
  from  sample
/

STR               DELIMITER   POSITION SUB_STR
----------------- --------- ---------- ----------
one***two***three ***                2 two
abc@@def@@ghi     @@                 3 ghi
abc@@def@@ghi     @@                -3 abc

SQL>

SY.

Report message to a moderator

Re: Extract string between 2 text [message #684918 is a reply to message #684917]

Sun, 26 September 2021 10:22

guddu_12
Messages: 223
Registered: April 2012
Location: UK

Senior Member

Thanks you Solomon Yakobson,

Solution seems to be working fine, in my first post i tried to create genric problem statement but my phrasing was wrong.

Solution is working fine for different set of data as well

Report message to a moderator

Previous Topic:	Oracle R12 query to retrieve a list of AP invoices and the corresponding file name/path
Next Topic:	SQL Query- Connect By (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Mar 25 10:07:26 CDT 2023