-2

Hi I need split text in Oracle SQL,

Input and output shown in picture. In first secture I have orginal data in one column ( in picture A column). In second secture which I need to return back(output).

my query is :

select val,
      CASE 
            when substr(val, 1, instr(val, '/') - 1)   is null then   val 
            ELSE substr(val, 1, instr(val, '/') - 1) 
      end as "LEVEL1",
      substr(VAL, 
             instr(val, '/',1)+1, 
             instr(val, '/',1,2)-instr(val, '/',1)-1) "LEVEL2",
      substr(VAL, instr(val, '/') ) "aparat3"
from rmtd1.split_row;

enter image description here

Improve this question
9
  • 1
    What have you tried so far? Always remember to show your working out Commented Oct 26, 2022 at 12:58
  • Here's an example using regexp_substr(input,'[^/]',1) Commented Oct 26, 2022 at 13:04
  • @DaveyBoy my query select val, CASE when substr(val, 1, instr(val, '/') - 1) is null then val ELSE substr(val, 1, instr(val, '/') - 1) end as "LEVEL1", substr(VAL, instr(val, '/',1)+1, instr(val, '/',1,2)-instr(val, '/',1)-1) "level2" from dual Commented Oct 26, 2022 at 13:04
  • 1
    @NurlanGanbarzada I'm not sure what you think I thought but it is the first result for that phrase and it works as long as you replace , with your desired /. If you find a problem with using the example, feel free to update the question describing what the problem is. Commented Oct 26, 2022 at 13:19
  • 2
    What is the problem with your current code (other than you're selecting from dual, not you real table; and dual doesn't have a val column)? It basically works. Please edit your question to show your sample data, expected results, current query and current results/error as formatted text (not images), and explain what issue you are having. Commented Oct 26, 2022 at 13:26

2 Answers 2

Reset to default
0

You don't really need more than regexp_substr()

select 
  val, 
  regexp_substr (val,'(^|/)([^/]*)',1, 1, null, 2) as level1,
  regexp_substr (val,'(^|/)([^/]*)',1, 2, null, 2) as level2,
  regexp_substr (val,'(^|/)([^/]*)',1, 3, null, 2) as level3,
  regexp_substr (val,'(^|/)([^/]*)',1, 4, null, 2) as level4
from your_table;
  1. (^|/) finds the beginning or a /
  2. ([^/]*) catches everything that comes after each hit from (^|/)
  3. 3rd parameter (1) tells it where to start in your input
  4. 4th parameter (1-4) selects which of your hits is returned
  5. 5th parameter lets you tweak how matches are determined
  6. last parameter selects the hit from your second subexpression ([^/]*) in point 2. above

demo

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Another method for arguments sake.

WITH tbl(val) AS (
SELECT 'aaa/bbbas/cc/dd' FROM dual UNION ALL
SELECT 'xzz/cca' FROM dual UNION ALL
SELECT 'dddddddd/xsssda/asasasasq' FROM dual UNION ALL
SELECT 'ddtrr5fd5sdwe' FROM dual
)
SELECT REGEXP_SUBSTR(val, '(.*?)(/|$)', 1, 1, NULL, 1) AS col_1,
REGEXP_SUBSTR(val, '(.*?)(/|$)', 1, 2, NULL, 1) AS col_2,
REGEXP_SUBSTR(val, '(.*?)(/|$)', 1, 3, NULL, 1) AS col_3,
REGEXP_SUBSTR(val, '(.*?)(/|$)', 1, 4, NULL, 1) AS col_4
FROM tbl;

DBFiddle

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.