Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: changing the output

    Hi

    Running an Oracle db. I need to query the db for some names. Is there any way I can omit the first 2 letters from those names in the result?

    So can I omit 'OM' from the names of the MSCs?

    Regards

    The query is:

    Code:
    SELECT  trunc(datetime,'hh') datetime, msc, CP
    
    FROM SCEHMA.TABLE A
    
    WHERE  a.DATETIME between trunc(sysdate,'hh')-1/24 and trunc(sysdate,'hh')-1/24/3600 
    AND ((MSC = 'OMHLR3') OR (MSC = 'OMMSC10') OR (MSC = 'OMMSC3') OR (MSC = 'OMMSC4') OR (MSC = 'OMMSC7') OR (MSC = 'OMMSC8') OR (MSC = 'OMMSC9'))
    
    group by trunc(datetime,'hh'), msc
    Last edited by shajju; 09-08-09 at 04:55.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Use SUBSTR

  3. #3
    Join Date
    Aug 2008
    Posts
    464

    substr

    Thanks Shammat.

    Kind of new to substr....so I take it instead of 'msc', I will write something like:

    substr('?',3,4) msc

    If there are multiple words (like OMHLR3, OMMSC3,OMMSC4,.....etc, to substring, then how would the line above be amended?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    SUBSTR accepts three arguments: the first one is your "input word" (OMHLR3, OMMSC3, ...). The second one is a starting position. As you want to omit the first two characters, its value should be 3. The third argument represents length of the returned string. As you want to get all characters except the first two, you can either omit it (and it will do the job by default), or calculate number of remaining characters (LENGTH function can do that).

    So:
    Code:
    SQL> select substr('OMHLR3', 3),
      2         substr('OMHLR3', 3, length('OMHLR3')),
      3         substr('OMHLR3', 3, length('OMHLR3') - 2)
      4  from dual;
    
    SUBS SUBS SUBS
    ---- ---- ----
    HLR3 HLR3 HLR3
    
    SQL>

  5. #5
    Join Date
    Aug 2008
    Posts
    464

    Input word

    Thanks.

    But my question was, if I have multiple 'input words' being returned by the query then how can I apply substr to all of them?

    Like my query returns, 10 MSCs with difference names but all starting with OM.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Simply, include result into your query:
    Code:
    SQL> l
      1  with test as
      2    (select 'OMHLR3' word from dual
      3     union
      4     select 'OMMSC3' from dual
      5     union
      6     select 'OMMSC4' from dual
      7    )
      8  select substr(word, 3) result
      9* from test
    SQL> /
    
    RESU
    ----
    HLR3
    MSC3
    MSC4
    
    SQL>

  7. #7
    Join Date
    Aug 2008
    Posts
    464
    Thanks but it hasn't worked for me.

    It's given me 7 rows with each MSC having the same CP_USAGE. Normally I get 6 rows (MSCs) with their own CP_USAGE.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by shajju
    It's given me 7 rows with each MSC having the same CP_USAGE.
    Show us the SQL that you are using....

  9. #9
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Here's a query based on what you gave us. Not sure where you were going with the cp column, so I used sum. And the first column was a constant due to the where constraints.

    Code:
    SELECT trunc(SYSDATE,'hh') - 1/24 AS datetime, 
      substr(msc,3) AS msc, sum(cp) AS cp
    FROM scehma.table a
    WHERE a.datetime >= trunc(SYSDATE,'hh') - 1/24 
    AND a.datetime < trunc(SYSDATE,'hh')
    AND msc IN ('OMHLR3','OMMSC10', 'OMMSC3','OMMSC4','OMMSC7','OMMSC8','OMMSC9')
    GROUP BY msc

  10. #10
    Join Date
    Aug 2008
    Posts
    464

    Much Appreciated!!!!!

    Many Many Thanks to both of you...I've got what I was after.

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    i guess you could use replace, but substr would handle all prefix occurances better most likely.

    PHP Code:
    duck@db01select 'OMHLR3' from dual;
    OMHLR3

    duck
    @db01select replace('OMHLR3','OM',''from dual;
    HLR3 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •