0

I have the following field in my DB (Oracle)

Jayson,1990,3,july

And i would like to get all the values here. What would be the solution?

Michey
  • 3
  • 2
  • What have you try? Can you put your code in the question? – DaFois May 17 '18 at 07:59
  • Duplicate of [Split comma separated values to columns in Oracle](https://stackoverflow.com/questions/31464275/split-comma-separated-values-to-columns-in-oracle) – Kaushik Nayak May 17 '18 at 08:15

3 Answers3

1
 SELECT CASE WHEN rn = 1 THEN 'Name: '
                      WHEN rn =2 THEN 'Year: '
                      WHEN rn = 3 THEN 'Day: '
                      WHEN rn = 4 THEN 'Month: ' END || result "Results"
                      FROM
 (
 WITH TEST (col) AS
 (SELECT 'Jayson,1990,3,july' FROM dual)
 SELECT REGEXP_SUBSTR(col, '[^,]+', 1, LEVEL) result, ROWNUM rn
FROM TEST
connect BY LEVEL <= REGEXP_COUNT(col, ',') + 1
);

enter image description here

0

I would like to get all the values here

Where is "here"?

Something like this, perhaps?

SQL> with test (col) as
  2    (select 'Jayson,1990,3,july' from dual)
  3  select regexp_substr(col, '[^,]+', 1, level) result
  4  from test
  5  connect by level <= regexp_count(col, ',') + 1;

RESULT
------------------
Jayson
1990
3
july

SQL>

[EDIT, after seeing the comment]

Two simple options: one is to use REGEXP_SUBSTR, another one is to use traditional SUBSTR + INSTR combination:

SQL> with test (col) as
  2    (select 'Jayson,1990,3,july' from dual)
  3  select
  4    regexp_substr(col, '\w+', 1, 1) name,
  5    regexp_substr(col, '\w+', 1, 2) year,
  6    regexp_substr(col, '\w+', 1, 3) day,
  7    regexp_substr(col, '\w+', 1, 1) month,
  8    --
  9    substr(col, 1, instr(col, ',', 1, 1) - 1) name_2,
 10    substr(col, instr(col, ',', 1, 1) + 1, instr(col, ',', 1, 2) - instr(col, ',', 1, 1) - 1) year_2,
 11    substr(col, instr(col, ',', 1, 2) + 1, instr(col, ',', 1, 3) - instr(col, ',', 1, 2) - 1) day_2,
 12    substr(col, instr(col, ',', 1, 3) + 1) month_2
 13  from test;

NAME   YEAR D MONTH  NAME_2 YEAR D MONT
------ ---- - ------ ------ ---- - ----
Jayson 1990 3 Jayson Jayson 1990 3 july

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
0

Sorry for bad description. I want to get firstly the first name as FirstName , then the date as Date ,then the day as Day.So i want to return me each time one row.I will execute it 4 times to retrive all results

RESULT:

Name:
Jayson

Date:
 1990

Day:
3
Michey
  • 3
  • 2