Home » SQL & PL/SQL » SQL & PL/SQL » Function Finding multiple occurrences of a string (19c)
Function Finding multiple occurrences of a string [message #684840] Mon, 06 September 2021 14:06 Go to next message
Unclefool
Messages: 82
Registered: August 2021
Member
I'm looking to create a function where I pass in a string and it returns the start and end position of the string along with the pattern I'm searching for.

Where I'm a bit confused is I know you can search from the start of the string or the end of the string to find a position but how can I find the end position. In addition, how can I get more than one occurrence of the string?

In my example below there are 2 occurrences of the string 'hello'. How can I find ALL 'N' occurrences? Would I need to use INSTR and a LENGTH command?

I know parsing out the input string is redundant but I want there to help verify the results.

Any help would be greatly appreciated. Btw I'm testing on live SQL if anyone wants to emulate my environment


create table data(
 str VARCHAR2(100)
);

INSERT into data (str) VALUES ('123hellphello321hello64');

Expected outcome 
 start_pos end_pos str
 9 13 hello
 16 20 hello

[Updated on: Mon, 06 September 2021 14:37]

Report message to a moderator

Re: Function Finding multiple occurrences of a string [message #684841 is a reply to message #684840] Mon, 06 September 2021 14:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I know you can search from the start of the string [...] to find a position but how can I find the end position

The end is just the start + the length.

Quote:
In addition, how can I get more than one occurrence of the string?

Use a row generator (hint: search for "regexp_count").

Re: Function Finding multiple occurrences of a string [message #684842 is a reply to message #684841] Mon, 06 September 2021 15:24 Go to previous messageGo to next message
Unclefool
Messages: 82
Registered: August 2021
Member
The start+length I understand but how can I read through the entire string to find multiple start positions
Re: Function Finding multiple occurrences of a string [message #684843 is a reply to message #684840] Mon, 06 September 2021 16:52 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
select  start_pos,
        start_pos + length('hello') - 1 end_pos,
        'hello' str
  from  data,
        lateral(
                select  instr(str,'hello',1,level) start_pos
                  from  dual
                  connect by instr(str,'hello',1,level) > 0
               )
/

 START_POS    END_POS STR
---------- ---------- -----
         9         13 hello
        17         21 hello

SQL>
SY.
Re: Function Finding multiple occurrences of a string [message #684844 is a reply to message #684843] Mon, 06 September 2021 21:23 Go to previous message
Unclefool
Messages: 82
Registered: August 2021
Member
@SY perfect thanks.
Previous Topic: query to display employee whose code is 03 is not inserted
Next Topic: Slab Level query (merged)
Goto Forum:
  


Current Time: Thu Mar 28 06:53:23 CDT 2024