How can I break an string in different pieces, and shift each piece split one level down?

Question

Using SQL ORACLE:

I need to break a series of strings into separate pieces and each subsequent piece needs to fall below the one before it, i.e. Listed vertically, one piece per row. I want to separate the strings at the '^' symbol.

For example, The string is 'RT12^RT45^LT44^TR55'

The result needs to be:

RT12
RT45
LT44
TR55

I'm not sure what "shift one level down" means if you want the result to have a single row. Do you just want to replace the carat character `^` with a carriage return (CR) or carriage return and line feed (CR LF) (Windows and Unix have a different line terminator)? — Justin Cave, Oct 31 '19 at 02:29
Also, why is the last line `TR^55`? Why wouldn't you split that on the caret? — Justin Cave, Oct 31 '19 at 02:41
use replace or translate function to replace ^ to a cr lf character，or do it with java code ,it is more flexible — ChenZhou, Oct 31 '19 at 03:19
Check this [post](https://stackoverflow.com/questions/28677070/split-function-in-oracle-to-comma-separated-values-with-automatic-sequence) — Arun Palanisamy, Oct 31 '19 at 07:06

score 3 · Accepted Answer · answered Oct 31 '19 at 06:45

3

 SELECT
       REGEXP_SUBSTR(TRIM('^' FROM 'RT12^RT45^LT44^TR55'), '[^\^]+', 1, LEVEL) AS res
   FROM
      DUAL
  CONNECT BY
     LEVEL <= REGEXP_COUNT(TRIM('^' FROM 'RT12^RT45^LT44^TR55'), '\^')+1

answered Oct 31 '19 at 06:45

shubham

99
9

combine your answers into one (or even better to delete the other, this is already pretty nice) – Barbaros Özhan Oct 31 '19 at 07:21

How can I break an string in different pieces, and shift each piece split one level down?

1 Answers1