SQL question

[matthew00]

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

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,

[stecal]

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.

[matthew00]

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

[stecal]

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

[matthew00]

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

Thanks Stecal, you have a great weekend.

[matthew00]

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:

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

[stecal]

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

[stecal]

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