-
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
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.
Last edited by stecal; 05-06-2011 at 10:31 AM.
-
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
-
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:
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|