Home / Questions / 524613
Accepted
Wiska
Asked: 2022-08-29 19:25:17 +0800 CST 2022-08-29 19:25:17 +0800 CST

How to separate a VARCHAR2 record from commas in PL/SQL?

  • 772

I have a food record:

pollo, carne, pez, res, zanahoria

What I would like is to use a cursor to separate each food and enter each one in a new record

pollo
carne
pez
res
zanahoria

I'm using Oracle 11g - temporary tables - PL/SQL and this is more or less the idea:

CURSOR MIC IS 
        SELECT UPPER(ALIMENTOS) FROM COMIDAS

BEGIN
FOR I IN MIC LOOP
sql

1 Answers

  1. Best Answer

    There are multiple ways to do it. A relatively simple one is to use CONNECT BYand REGEXP_SUBSTR(). CONNECT BYallows you to recursively go through the chain, while REGEXP_SUBSTR()extracting the elements of it.

    WITH rws AS (
  SELECT alimentos FROM Comidas
)
  SELECT LTRIM( regexp_substr (
           alimentos,
           '[^,]+',
           1,
           level
         )) value
  FROM rws
  CONNECT BY level <= 
    LENGTH( alimentos) - LENGTH( REPLACE( alimentos, ',' ) ) + 1;
    • 2