If you know for sure that you will cutting off everything from the first period on, you select the substring of the output. If you know that "blah" will always be X number of characters long, you can approach it that way.
select substr(t.target_name, 1, instr(t.target_name,'.',1,1)-1)
from the rest of your statement....
The instr starts at position 1, finds the first occurrence of a period, which results in a position value (number), then back that off by one, which then gives the length for the substring.
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);
What about the "_clk" part? If you just want the SID part of your string, find the first underscore => that position + 1 is the start of substr. Find the second underscore's position. Then do the math to determine length of the SID component.
start = underscore position plus one
length = position of second underscore - position of first underscore - 1