CODEWITHSUNDEEP
sqlsplitoracle-sqldeveloper
Owunn
Owunn

Reputation: 13

Split values from a column to another column SQL DEVELOPER

Hello people here again with another oracle SQL question.

Im having some problems spliting values from a column to another one.

So there it goes.. im having this query :

SELECT MONEDA , 
LISTAGG (MONTO , ';') WITHIN GROUP (ORDER BY MONTO) MONTO,
REGEXP_SUBSTR(MONTO, '[^;]+', 1, 1) col_one,
REGEXP_SUBSTR(MONTO, '[^;]+', 1, 2) col_two  
FROM (SELECT SUM(ZMT.AMOUNT) AS MONTO,
       ZMT.T_TYPE AS tipo,
       JSON_VALUE(MSG, '$.glAccount.currency.code') AS moneda
            FROM Z_MAMBU_TRANSACTIONS ZMT JOIN POSTING_ONLINE0182 PO ON PO.RESP_REFERENCE0182 = ZMT.TRANSACTIONID
            WHERE TO_CHAR(ZMT.CREATIONDATE, 'YYYY-MM-DD') = '2021-04-20' AND
                  PO.POSTING_RESPCODE0182 = 0 AND
                  (JSON_VALUE(MSG, '$.type') = 'DEBIT') OR (JSON_VALUE(MSG, '$.type') = 'CREDIT')
            GROUP BY T_TYPE, JSON_VALUE(MSG, '$.glAccount.currency.code')
            ORDER BY T_TYPE)
GROUP BY MONEDA

the result is the next:

https://i.sstatic.net/QMgYr.png

What i need to do is SPLIT the "MONTO" values with the ";" as separator to other 2 columns (col_one and col_two). As you can see in the result he is spliting me only the second value not the first.

After that i need to make the substract from the values that i split.

This is an exaple of what i need :

MONEDA  MONTO                             COL_ONE            COL_TWOV
COL     174579148065,39;175491229711,9    174579148065,39    175491229711,9  
DOL     30300300300;30300300300           30300300300        30300300300

THANK YOU GUYS!

Upvotes: 0

Views: 140

Answers (2)

Littlefoot
Littlefoot

Reputation: 142958

I agree with Tim - substr + instr do the job just nicely. If you, for some reason, want to try regular expressions, see if this helps (sample data in lines #1 - 4; query begins at line #5):

SQL> with result (moneda, monto) as
  2    (select 'COL', '174579148065,39;175491229711,9' from dual union all
  3     select 'DOL', '30300300300;30300300300'        from dual
  4    )
  5  select moneda,
  6         regexp_substr(monto, '\d+(,\d+)?', 1, 1) col_one,
  7         regexp_substr(monto, '\d+(,\d+)?', 1, 2) col_two
  8  from result;

MONEDA     COL_ONE              COL_TWO
---------- -------------------- --------------------
COL        174579148065,39      175491229711,9
DOL        30300300300          30300300300

SQL>

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521987

I would just use the base string functions here and avoid regex altogether. Going by your sample data given at the very end of your question:

SELECT
    MONEDA,
    MONTO,
    SUBSTR(MONTO, 1, INSTR(MONTO, ';') - 1) AS COL_ONE,
    SUBSTR(MONTO, INSTR(MONTO, ';') + 1, LENGTH(MONTO) - INSTR(MONTO, ';')) AS COL_TWO
FROM yourTable;

screen capture from demo link below

Demo

Upvotes: 1

Related Questions