| Function Finding multiple occurrences of a string [message #684840] |
Mon, 06 September 2021 14:06  |
 |
Unclefool
Messages: 65
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 #684843 is a reply to message #684840] |
Mon, 06 September 2021 16:52  |
Solomon Yakobson
Messages: 3212
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>
|
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
