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
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> We can use REGEXP_REPLACE here as one option:
SELECT REGEXP_REPLACE(MARKSHEET_ID, '_[^_]+_[^_]+$', '') AS NEW_MARKSHEET_ID FROM yourTable; 