Home » SQL & PL/SQL » SQL & PL/SQL » divide text string into different columns
divide text string into different columns [message #669993] |
Mon, 28 May 2018 21:54 |
|
suji6281
Messages: 145 Registered: September 2014
|
Senior Member |
|
|
Hi All,
Could you please help me with SQL Query to fetch output as below.
CREATE TABLE MY_TBL1 (DESCR VARCHAR(100));
INSERT INTO MY_TBL1 VALUES ('update,PO_ORDER');
INSERT INTO MY_TBL1 VALUES ('delete,INVOICE');
INSERT INTO MY_TBL1 VALUES ('update,FORMS/FORM/ID=WTHD_INFO');
INSERT INTO MY_TBL1 VALUES ('add,FORMS/FORM/ID=ACCTOUNTS');
INSERT INTO MY_TBL1 VALUES ('update,FORMS/FORM/ID=WTHD_INFO/rec/fld/id=45691');
OUTPUT:
update,PO_ORDER
delete,INVOICE
update,FORMS/FORM/ID=WTHD_INFO
add,FORMS/FORM/ID=ACCTOUNTS
update,FORMS/FORM/ID=WTHD_INFO/rec/fld/id=45691
I've tried and output showing as below:
SELECT SUBSTR (DESCR, 1, INSTR(DESCR,',')-1), SUBSTR (DESCR, INSTR(DESCR,',')+1, LENGTH(DESCR)) FROM MY_TBL1;
update,PO_ORDER
delete,INVOICE
update,FORMS/FORM/ID=WTHD_INFO
add,FORMS/FORM/ID=ACCTOUNTS
update,FORMS/FORM/ID=WTHD_INFO/rec/fld/id=45691
I'm expecting the output would be as below:
update,PO_ORDER
delete,INVOICE
update,WTHD_INFO
add,ACCTOUNTS
update,WTHD_INFO,45691
Thank You.
Regards
Suji
|
|
|
Re: divide text string into different columns [message #669994 is a reply to message #669993] |
Mon, 28 May 2018 23:19 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
It helps if you also explain in words what the rules are for the results that you want. It looks like you want to replace what looks like paths with / in them and ending with = with a comma. If that is what you want, then you should be able to use something like below.
SCOTT@orcl_12.1.0.2.0> SELECT SUBSTR (DESCR, 1, INSTR(DESCR,',')-1) AS column1,
2 LTRIM
3 (REGEXP_REPLACE
4 (SUBSTR (DESCR, INSTR(DESCR,','), LENGTH(DESCR)),
5 '[/,][^=]+=',
6 ','),
7 ',') AS column2
8 FROM MY_TBL1
9 /
COLUMN1 COLUMN2
---------- ----------------------------------------
update PO_ORDER
delete INVOICE
update WTHD_INFO
add ACCTOUNTS
update WTHD_INFO,45691
5 rows selected.
|
|
|
|
|
Re: divide text string into different columns [message #670138 is a reply to message #670137] |
Sun, 10 June 2018 10:42 |
Solomon Yakobson
Messages: 3285 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
select regexp_substr(descr,'\w+') column1,
regexp_substr(descr,',(\w+)',1,1,null,1) column2,
regexp_substr(descr,' = (\w+)',1,1,null,1) column3,
regexp_substr(descr,'=(\w+)',1,1,null,1) column4
from my_tbl1
/
COLUMN1 COLUMN2 COLUMN3 COLUMN4
---------- ---------- --------------- ----------
update Forms BANKINGDTLS 23678
update alias 56841
SQL>
SY.
|
|
|
|
|
Re: divide text string into different columns [message #670141 is a reply to message #670140] |
Sun, 10 June 2018 13:54 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
If the first "=" is alphabetical and the second one digits then it can be:
SQL> select regexp_substr(descr,'\w+') column1,
2 regexp_substr(descr,',(\w+)',1,1,null,1) column2,
3 regexp_substr(descr,'=([a-z_]+)',1,1,'i',1) column3,
4 regexp_substr(descr,'=(\d+)',1,1,null,1) column4
5 from my_tbl1
6 /
COLUMN1 COLUMN2 COLUMN3 COLUMN4
--------------- --------------- --------------- ---------------
update PO_ORDER
delete INVOICE
update FORMS WTHD_INFO
add FORMS ACCTOUNTS
update FORMS WTHD_INFO 45691
update Forms BANKINGDTLS 23678
update alias 56841
[Updated on: Sun, 10 June 2018 13:55] Report message to a moderator
|
|
|
|
Re: divide text string into different columns [message #670143 is a reply to message #670140] |
Sun, 10 June 2018 17:04 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Your rules do not match your desired output.
You said, "... if string has only one = in it then the word after = should be in column 4 ..."
However, your desired output does not show that:
COLUMN1 COLUMN2 COLUMN3 COLUMN4
...
update FORMS WTHD_INFO NULL
add FORMS ACCTOUNTS NULL
...
The following follows your stated rules, but not your desired output:
SCOTT@orcl_12.1.0.2.0> SET NULL NULL
SCOTT@orcl_12.1.0.2.0> COLUMN column1 FORMAT A15 WORD_WRAPPED
SCOTT@orcl_12.1.0.2.0> COLUMN column2 FORMAT A15 WORD_WRAPPED
SCOTT@orcl_12.1.0.2.0> COLUMN column3 FORMAT A15 WORD_WRAPPED
SCOTT@orcl_12.1.0.2.0> COLUMN column4 FORMAT A15 WORD_WRAPPED
SCOTT@orcl_12.1.0.2.0> SELECT REGEXP_SUBSTR (descr, '[^,]+') column1,
2 REGEXP_SUBSTR (descr, ',(\w+)', 1, 1, null, 1) column2,
3 DECODE
4 (REGEXP_COUNT (descr, '='),
5 2, REGEXP_SUBSTR (descr, '=(\w+)', 1, 1, null, 1),
6 NULL) column3,
7 DECODE (REGEXP_COUNT (descr, '='),
8 1, REGEXP_SUBSTR (descr, '=(\w+)', 1, 1, null, 1),
9 2, REGEXP_SUBSTR (descr, '=(\w+)', 1, 2, null, 1),
10 NULL) column4
11 FROM my_tbl1
12 /
COLUMN1 COLUMN2 COLUMN3 COLUMN4
--------------- --------------- --------------- ---------------
update PO_ORDER NULL NULL
delete INVOICE NULL NULL
update FORMS NULL WTHD_INFO
add FORMS NULL ACCTOUNTS
update FORMS WTHD_INFO 45691
update Forms BANKINGDTLS 23678
update alias NULL 56841
7 rows selected.
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:42:30 CDT 2024
|