Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Jim.

Asked: February 07, 2017 - 9:50 pm UTC

Last updated: February 18, 2017 - 4:31 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked 

Hello folks -

Here's my database & server info:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production           
PL/SQL Release 12.1.0.2.0 - Production                        
CORE    12.1.0.2.0      Production                   
TNS for Linux: Version 12.1.0.2.0 - Production           
NLSRTL Version 12.1.0.2.0 - Production                                     
O.S.:  Redhat 6 (x86_64 x86_64 GNU/Linux)

Can you help write a query that will produce the number of rows of the maximum column list (Val2 in this case), populating NULLs in the results for the other two parameters (that have less results than Val2)? I realize this probably violates the intended usage of CONNECT BY, but this is as close as I've come so far:  


SELECT REGEXP_SUBSTR (',2,3,7',  '[^,]+', 1, LEVEL)      Val1,
       REGEXP_SUBSTR (',1,2,8,9',  '[^,]+', 1,  LEVEL)   Val2,
       REGEXP_SUBSTR (',7,4',  '[^,]+', 1,  LEVEL)       Val3    
FROM DUAL
  CONNECT BY LEVEL  <= REGEXP_COUNT( ',2,3,7', ',')   
  CONNECT BY  LEVEL <= REGEXP_COUNT( ',1,2,8,9', ',') 
  CONNECT BY LEVEL  <= REGEXP_COUNT( ',7,4', ',')                          
/

I need an output like this:

VAL1   VAL2     VAL3
------ -------- ----
2      1        7
3      2        4
7      8
       9       

4 rows selected.


Thx in advance,
Jim

and Connor said...

You were super close :-)

SQL> SELECT REGEXP_SUBSTR (',2,3,7',  '[^,]+', 1, LEVEL)      Val1,
  2         REGEXP_SUBSTR (',1,2,8,9',  '[^,]+', 1,  LEVEL)   Val2,
  3         REGEXP_SUBSTR (',7,4',  '[^,]+', 1,  LEVEL)       Val3
  4  FROM DUAL
  5    CONNECT BY LEVEL  <=
  6      greatest( REGEXP_COUNT( ',2,3,7', ',')
  7               ,REGEXP_COUNT( ',1,2,8,9', ',')
  8               ,REGEXP_COUNT( ',7,4', ',')
  9               )
 10  /

VAL1                     VAL2                             VAL3
------------------------ -------------------------------- ------------
2                        1                                7
3                        2                                4
7                        8
                         9


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, February 17, 2017 - 1:36 pm UTC

SQL> SELECT REGEXP_SUBSTR (',2,3,7', '[^,]+', 1, LEVEL) Val1,
2 REGEXP_SUBSTR (',1,2,8,9', '[^,]+', 1, LEVEL) Val2,
3 REGEXP_SUBSTR (',7,4', '[^,]+', 1, LEVEL) Val3
4 FROM DUAL
5 CONNECT BY LEVEL <=
6 greatest( REGEXP_COUNT( ',2,3,7', ',')
7 ,REGEXP_COUNT( ',1,2,8,9', ',')
8 ,REGEXP_COUNT( ',7,4', ',')
9 )
10 /

VAL1 VAL2 VAL3
------------------------ -------------------------------- ------------
2 1 7
3 2 4
7 8
9

In this case if i don't know the how many values are there in particular column,so in this case shall i use the column_name within the expression or what?
Connor McDonald
February 18, 2017 - 4:31 am UTC

Yes, use the column name.

SQL> create table t  as
  2  select ',2,3,7' c1,
  3   ',1,2,8,9' c2,
  4   ',7,4' c3
  5   from dual;

Table created.

SQL>
SQL> SELECT REGEXP_SUBSTR (c1,  '[^,]+', 1, LEVEL)      Val1,
  2             REGEXP_SUBSTR (c2,  '[^,]+', 1,  LEVEL)   Val2,
  3             REGEXP_SUBSTR (c3,  '[^,]+', 1,  LEVEL)       Val3
  4      FROM t
  5        CONNECT BY LEVEL  <=
  6          greatest( REGEXP_COUNT( c1, ',')
  7                   ,REGEXP_COUNT( c2, ',')
  8                   ,REGEXP_COUNT( c3, ',')
  9                   )
 10     /

VAL1   VAL2     VAL3
------ -------- ----
2      1        7
3      2        4
7      8
       9


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.