Using Instr to extract path portion.

[system]

Hi all,

I am trying to use the Instr function in Oracle in order to extract the folder part,in order to do that i need to get rid of the ‘’ sign but to keep the text between every ‘’ sign.

Example:

folder_part1\folder_part2\folder_part3\

The outcome should look like:

folder_part1
folder_part2
folder_part3

Any ideas ?

Thanks

Yoav

---

yohab (BOB member since 2003-12-24)

[MarkP]

If you want it output as three separate rows, you will need to do so at the database level unless there is always a known maximum number of options.

[system]

The result can appear as a separate column.

I have maximum 10 positions of the folder path.

Thanks

Yoav

---

yohab (BOB member since 2003-12-24)

[MarkP]

It will get a bit messy with 10, but have a read through this thread; Dave’s post should help:

120×120 10.4 KB

Hello I have trawled the internet and this site and tried (unsucessfully) to split the pipe delimited field that I have into separate fields. So far, thanks to some code that I found on google, I have managed to get the last 2 values, and can get the first, It is just all the bits in the middle that I seem to be getting stuck with. I am using a SQL Server 2008 database and would, ideally, like this to be a Universe solution rather than using substring and Pos in the report itself. Here is wh…

[system]

Thanks Mark,

i am looking for a similar logic in Oracle

i’ll see if i can implement it in Oracle.
Yoav

---

yohab (BOB member since 2003-12-24)

[system]

This is code i used:

SELECT regexp_substr(pth,’[^]+’,1,1)
,regexp_substr(pth,’[^]+’,2,2)
,regexp_substr(pth,’[^]+’,3,3)

add as many pieces you need if you have a longer text that need to be separated.

Thanks

Yoav

---

yohab (BOB member since 2003-12-24)