Use function on column and then use another function on output of new column in Oracle

Question

I've been trying to figure out a way that I can use a function on a column to get a certain output back (within a column) and then additionally use another function on the column that I previously got output from in another column. Sounds confusing right? Let me show you an example to make it a little bit easier to understand.

SELECT PAGE, substr(PAGE, 1, instr(PAGE, ';', -1) - 1) PAGE2, substr(PAGE2, instr(PAGE2, '/', -1)+1) PAGE_NEW
FROM 
    SNETP001.INGACCESS_VIEWS INGACCESS_VIEWS,
     SNETP001.INGACCESS_PAGEID INGACCESS_PAGEID

PAGE is the originating column from the table PAGE2 is the column created using the SUBSTR and nested INSTR function PAGE_NEW is the additional column that I wanted to run on PAGE2 column.

Any thoughts on this? I can try to provide additional information if needed.

Thanks, Ryan

vishad · Accepted Answer · 2014-04-16 17:56:22Z

1

You can do it by using a subquery as :

Select PAGE, PAGE2, substr(PAGE2, instr(PAGE2, '/', -1)+1) PAGE_NEW
 FROM
 ( SELECT PAGE, substr(PAGE, 1, instr(PAGE, ';', -1) - 1) PAGE2
  FROM 
   SNETP001.INGACCESS_VIEWS INGACCESS_VIEWS,
   SNETP001.INGACCESS_PAGEID INGACCESS_PAGEID)

Or you can write the substr function twice on the PAGE column as below:

   SELECT PAGE, substr(PAGE, 1, instr(PAGE, ';', -1) - 1) PAGE2, 
   substr(substr(PAGE,   1, instr(PAGE, ';', -1) - 1),
          instr(substr(PAGE, 1, instr(PAGE, ';', -1) - 1), '/', -1)+1)  PAGE_NEW
   FROM 
     SNETP001.INGACCESS_VIEWS INGACCESS_VIEWS,
     SNETP001.INGACCESS_PAGEID INGACCESS_PAGEID

Hope i haven't missed something here in your req.

Vishad

answered Apr 16, 2014 at 17:56

vishad

1,1647 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use function on column and then use another function on output of new column in Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related