CODEWITHSUNDEEP
sqloracle-database
Deepu298
Deepu298

Reputation: 75

Extract few letters from the column (values are in string)

Need to extract last three letters of 'CLASS' column and last 6 letters depending on the rows. In SQL Developer.

I have the following table-

ROW_NO    CLASS
   1      BLUE_ALL
   2      GREEN_ALL_SET             
   3      WHITE_ALL
   4      RED_ALL_SET
   5      YELLOW_ALL
   6      PURPLE_ALL_SET

I need to create third column called CATEGORY as show below 

ROW_NO    CLASS            CATEGORY
   1      BLUE_ALL         ALL
   2      GREEN_ALL_SET    ALL_SET             
   3      WHITE_ALL        ALL
   4      RED_ALL_SET      ALL_SET
   5      YELLOW_ALL       ALL
   6      PURPLE_ALL_SET   ALL_SET

Thanks

I tried using combination of SUBSTR and INSTR functions

Upvotes: 0

Views: 111

Answers (4)

user5683823
user5683823

Reputation:

At least based on the sample data you provided, it seems that in every case you want to extract everything AFTER the first underscore.

If so:

select row_no, class, substr(class, instr(class, '_') + 1) as category
from   your_table
order  by row_no  --  if needed
;

DEMO:

with
  your_table (row_no, class) as (
    select 1, 'BLUE_ALL'       from dual union all
    select 2, 'GREEN_ALL_SET'  from dual union all             
    select 3, 'WHITE_ALL'      from dual union all
    select 4, 'RED_ALL_SET'    from dual union all
    select 5, 'YELLOW_ALL'     from dual union all
    select 6, 'PURPLE_ALL_SET' from dual
  )
select row_no, class, substr(class, instr(class, '_') + 1) as category
from   your_table
order  by row_no  --  if needed
;

    ROW_NO CLASS          CATEGORY      
---------- -------------- --------------
         1 BLUE_ALL       ALL           
         2 GREEN_ALL_SET  ALL_SET       
         3 WHITE_ALL      ALL           
         4 RED_ALL_SET    ALL_SET       
         5 YELLOW_ALL     ALL           
         6 PURPLE_ALL_SET ALL_SET

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269553

This looks more like conditional logic:

select (case when class like '%\_%\_%' then substr(class, -6)
             else substr(class, -3)
        end)

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

One option would be applying regexp_substr() analytic function and listagg() concatenation consecutively :

with t2 as
(
 Select Regexp_Substr(class, '[^_]+',1,level+1) as str, row_no, class, level as lvl
   From t
Connect By level <= Regexp_Count(class, '_')
and prior row_no = row_no and prior sys_guid() is not null
)
Select row_no, class, listagg(str, '_') within group ( order by lvl ) as category
  From t2
 Group By row_no, class;

ROW_NO  CLASS           CATEGORY
------  -------------   --------
1       BLUE_ALL        ALL
2       GREEN_ALL_SET   ALL_SET
3       WHITE_ALL       ALL
4       RED_ALL_SET     ALL_SET
5       YELLOW_ALL      ALL
6       PURPLE_ALL_SET  ALL_SET

Demo

Upvotes: 1

forpas
forpas

Reputation: 164069

Seems like you have only _ALL_SET and _SET suffixes, so take last 7 or 3 chars: 

select row_no, class,
  substr(class, case when class like '%\_ALL\_SET' escape '\' then -7 else -3 end) category
from tablename

See the demo.
Results:

ROW_NO  CLASS               CATEGORY
1       BLUE_ALL            ALL
2       GREEN_ALL_SET       ALL_SET
3       WHITE_ALL           ALL
4       RED_ALL_SET         ALL_SET
5       YELLOW_ALL          ALL
6       PURPLE_ALL_SET      ALL_SET

Upvotes: 2

Related Questions