How to select column values untill the certain characters in ORACLE-CodePudding

I have a table column MARKSHEET_ID with values like:

2020/2021_A22_3_1_10_ACP302_0082
2020/2021_A22_3_1_10_ACR301_01
2020/2021_A22_3_1_10_ACR302_01

I want to select the count of the distinct values just untill the character before the fifth occurence of underscore.

In my example I want to get the count of 2020/2021_A22_3_1_10 as 1

CodePudding user response：

We can use REGEXP_REPLACE here as one option:

SELECT REGEXP_REPLACE(MARKSHEET_ID, '_[^_] _[^_] $', '') AS NEW_MARKSHEET_ID
FROM yourTable;

CodePudding user response：

As you said: count distinct values up to 5th underline:

SQL> with test (col) as
  2    (select '2020/2021_A22_3_1_10_ACP302_0082' from dual union all
  3     select '2020/2021_A22_3_1_10_ACR301_01'   from dual union all
  4     select '2020/2021_A22_3_1_10_ACR302_01'   from dual
  5    )
  6  select count(distinct(substr(col, 1, instr(col, '_', 1, 5)))) cnt
  7  from test;                             ^         ^      ^
                                            |         |      |
       CNT                              position   underline  5th
----------
         1

SQL>