Thanks Stecal,

yes, that's correct, I want to trim everything after the first period. When I run the query with your advise, I got ORA-1722: invalid number and I think it had issue somewhere front of the '.'

select substr(t.target_name, 1, instr(t.target_name,1,1,'.')-1)
FROM mgmt_targets t JOIN mgmt_current_severity s
ON s.target_guid = t.target_guid
WHERE t.target_type = 'oracle_database'
--and s.collection_timestamp=(select SYSDATE from dual);
Thanks again