By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with t0 as (
select '1@apple@1' as value
union all select '2@apple@2'
union all select '3@apple@4'
union all select '4@box@4'
union all select '5@box@5'
),
trimmed as (
select regexp_replace(value,'[0-9]*@(.+?)@[0-9]*','$1') as name
from t0
)
select name, count(*)
from trimmed
group by name
order by name
| name | count(*) |
|---|---|
| apple | 3 |
| box | 2 |