-1

I have a column having data in format : "abc.def.ghi.jkl" i want to write a query to split this for example I want to fetch "abc" of this string, since the length is not fixed for every value in the column I need some way to split this string using "." delimeter. please suggest some way

K.Shrivastava
  • 1

1 Answers1

1

you can use "regexp_substr" with "connect by" to get desired result as below:

select regexp_substr('abc.def.ghi.jkl','[^.]+', 1, level) as splitted_string
from dual
connect by regexp_substr('abc.def.ghi.jkl', '[^.]+', 1, level) is not null;
Vivek
  • 783
  • 5
  • 11
  • This is working but fetching only first word of the splitted string, I am looking for whole string till delimeter – K.Shrivastava Jun 19 '18 at 12:40
  • like "abc." "def." ? then you can concat the "." with string again – Vivek Jun 19 '18 at 12:42
  • This has issues if there are multiple input rows as the hierarchical query cannot correlate the next level with its parent so it will correlate it with all possible parents generating exponentially more and more duplicate rows as it descends the hierarchy. – MT0 Jun 19 '18 at 12:47
  • No by that I mean the string is like abc def ghi. pqr.xyz and the query only fetching abc but I need to fetch abc def ghi – K.Shrivastava Jun 20 '18 at 05:37