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 Go to next message
suji6281
Messages: 68
Registered: September 2014
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');


SELECT * FROM MY_TBL1;
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8848
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 #670074 is a reply to message #669994] Mon, 04 June 2018 00:55 Go to previous messageGo to next message
suji6281
Messages: 68
Registered: September 2014
Member
Thank you Barbara. Given sql works very well as per requirement.
Re: divide text string into different columns [message #670137 is a reply to message #670074] Sun, 10 June 2018 09:27 Go to previous messageGo to next message
suji6281
Messages: 68
Registered: September 2014
Member
Hi Barbara,

Could you please help me with sql query with modification. I am expecting output would be as below.
The word after 1st comma in 2ndcolumn, and the word after = in 3rd column and if 2nd = found in that string then the word after it would be in 4th column.
EXAMPLE:
INSERT INTO MY_TBL1 VALUES ('update,Forms/form/id = BANKINGDTLS/record/field/is=23678');
INSERT INTO MY_TBL1 VALUES('update,alias/id = 56841');
Expected Output as below:

COLUMN1 COLUMN2 COLUMN3 COLUMN4
---------- ---------- ---------- --------
update Forms BANKINGDTLS 23678
update alias 56841

Thank you

Regards
Suji
Re: divide text string into different columns [message #670138 is a reply to message #670137] Sun, 10 June 2018 10:42 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2756
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 #670139 is a reply to message #670138] Sun, 10 June 2018 11:52 Go to previous messageGo to next message
suji6281
Messages: 68
Registered: September 2014
Member
Thank You Solomon.

Could you please help me that if string has only one = in it then the word after = should be in column 4. Incase string has 2 = then the word after 1st = should in collumn 3 and the last word after 2nd = should be in column 4.

 	COLUMN1	COLUMN2	COLUMN3	        COLUMN4
	update	Forms	BANKINGDTLS	23678
	update	alias	                56841	
thank you.

Regards
Suji
Re: divide text string into different columns [message #670140 is a reply to message #670139] Sun, 10 June 2018 13:13 Go to previous messageGo to next message
suji6281
Messages: 68
Registered: September 2014
Member
Hi Solomon,

here is the sample data with Insert statements for my sql query.

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');
INSERT INTO MY_TBL1 VALUES ('update,Forms/form/id=BANKINGDTLS/record/field/is=23678');
INSERT INTO MY_TBL1 VALUES ('update,alias/id=56841');


Output should be as below:



  	COLUMN1	COLUMN2	  COLUMN3	COLUMN4
	update	PO_ORDER   NULL	        NULL
	delete	INVOICE	   NULL	        NULL
	update	FORMS	   WTHD_INFO	NULL
	add	FORMS	   ACCTOUNTS	NULL
	update	FORMS	   WTHD_INFO	45691
	update	Forms	   BANKINGDTLS  23678
	update	alias	   NULL	        56841


thank you.

Regards
Suji
Re: divide text string into different columns [message #670141 is a reply to message #670140] Sun, 10 June 2018 13:54 Go to previous messageGo to next message
Michel Cadot
Messages: 65545
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 #670142 is a reply to message #670140] Sun, 10 June 2018 16:38 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2756
Registered: January 2010
Location: Connecticut, USA
Senior Member
You keep changing input & desired output. Please provide rules, otherwise it is a guessing game.

SY.
Re: divide text string into different columns [message #670143 is a reply to message #670140] Sun, 10 June 2018 17:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8848
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.

Re: divide text string into different columns [message #670210 is a reply to message #670143] Mon, 18 June 2018 10:33 Go to previous message
suji6281
Messages: 68
Registered: September 2014
Member
Thank you all for your solutions. All of your solutions are working fine as per my requirement. Thank you.
Previous Topic: problem of inserting a long string of characters :ORA-22835: Taille de tampon insuffisante pour la c
Next Topic: Help Grouping by
Goto Forum:
  


Current Time: Mon Jun 25 07:28:35 CDT 2018