DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: SQL question

  1. #1
    Join Date
    Mar 2006
    Posts
    176

    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,

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    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.

  3. #3
    Join Date
    Mar 2006
    Posts
    176
    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

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    Fixed the order, sorry about that. It's substring, start, occurrence ('.',1,1)

  5. #5
    Join Date
    Mar 2006
    Posts
    176
    I was going to say I found the issue, but you were faster than me.

    Thanks Stecal, you have a great weekend.

  6. #6
    Join Date
    Mar 2006
    Posts
    176
    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

  7. #7
    Join Date
    May 2002
    Posts
    2,645
    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

  8. #8
    Join Date
    May 2002
    Posts
    2,645
    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
  •  


Click Here to Expand Forum to Full Width