Home » SQL & PL/SQL » SQL & PL/SQL » Extract string between 2 text (11gr2)
Extract string between 2 text [message #684912] Sun, 26 September 2021 05:47 Go to next message
guddu_12
Messages: 227
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
Re: Extract string between 2 text [message #684913 is a reply to message #684912] Sun, 26 September 2021 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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 Go to previous messageGo to next message
guddu_12
Messages: 227
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
Re: Extract string between 2 text [message #684915 is a reply to message #684914] Sun, 26 September 2021 07:25 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 Go to previous messageGo to next message
guddu_12
Messages: 227
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
Re: Extract string between 2 text [message #684917 is a reply to message #684916] Sun, 26 September 2021 09:43 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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.
Re: Extract string between 2 text [message #684918 is a reply to message #684917] Sun, 26 September 2021 10:22 Go to previous message
guddu_12
Messages: 227
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
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:
  


Current Time: Fri Mar 29 10:13:00 CDT 2024