CODEWITHSUNDEEP
sqloracle-databaseoracle11gcount
Mishti
Mishti

Reputation: 47

Need to split ; separated data into different rows and then get count for each row

there is a table TABLE01 with the below fields and values:

COL1         COL2  COL3  COL4   COL5   COL6   COL7
01-01-2017   P     123   abc    Y       T     A1_A2
01-21-2017   P     456   rst    N       P     A1_A3;A4
06-10-2017   P     876   hyi    N       S     A2_A8;A5_A6;A9
10-12-2017   P     999   iop    Y       R     A7

Now i need create a new table having columns(COL1, COL2, COL3, COL4, COL7 and COUNT_COL3) using above table by splititng all ';' seperated COL7 into different rows as under with count of each COL3

COL1         COL2  COL3  COL4   COL7     COUNT_COL3
01-01-2017   P     123   abc    A1_A2      1
01-21-2017   P     456   rst    A1_A3      2
01-21-2017   P     456   rst    A4         2
06-10-2017   P     876   hyi    A2_A8      3
06-10-2017   P     876   hyi    A5_A6      3
06-10-2017   P     876   hyi    A9         3
10-12-2017   P     999   iop    A7         1

I can do the splititng part and added 0 for count as not sure how to add count part

INSERT into TABLE02 (COL1, COL2,COL3,COL4,COL7,COUNT_COL3)
SELECT COL1, COL2, COL3,COL4,
          trim(COLUMN_VALUE) COL7, 0
FROM TABLE01
      xmltable(('"'
      || REPLACE(COL7, ';', '","')
      || '"'))
;

Can someone pls suggest?

Upvotes: 0

Views: 59

Answers (1)

user5683823
user5683823

Reputation:

It seems the number you add is the count of rows created by splitting, which is one more than the number of semicolons (;) in the string in COL7.

This can be done with 1 + regexp_count(COL7, ';'), which is easy to write and understand.

Old timers will tell you that standard string functions (as opposed to regular expressions) are often much faster. This is only important if performance is not satisfactory.

In this case, the count can be obtained by

1 + length(COL7) - length(replace(COL7, 'z;', 'z'))

That is: replace every ; with "nothing" in COL7 and see how much shorter the string becomes, that will tell you how many ; there were in the string. The extra 'z' in replace() is needed because replace() returns null if any argument (including the last one) is null. One of the five thousand oddities of Oracle... As written, the function will replace every occurrence of z with z and every ; with "nothing", so that's just what we want.

Upvotes: 0

Related Questions