SQL question

could someone please tell me how I can trim the below output to only the character that were before the .public.smp.com

Quote:

---

SELECT t.target_name
FROM mgmt_targets t JOIN mgmt_current_severity s
ON s.target_guid = t.target_guid
WHERE t.target_name LIKE '%blah%'
AND t.target_type = 'oracle_database'

---

Result: "blah.public.smp.com"

and I would only need "blah" on my output.

Thanks,

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.

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 '.'

Quote:

---

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

Fixed the order, sorry about that. It's substring, start, occurrence ('.',1,1)

I was going to say I found the issue, but you were faster than me.

Thanks Stecal, you have a great weekend.

Hi Stecal/all again

Here is my original result" "yum.ssl.com_SID_clk"

I need to make some change around the query,below is my query to cut the second underscore:

Quote:

---

SELECT substr(upper(t.target_name), 1, instr(t.target_name,'_',1,2)-2) AS db_name, message
FROM mgmt_targets

---

how can I cut "yum.ssl.com_"? that would leave me only SID.

thx

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

You don't need to trim it, per se. You just start the substring further along the positions.