Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2004
    Posts
    9

    Unhappy Unanswered: Plz solve this query

    chiranjeevi
    raveendra
    sreenivasarao
    koteswar
    manoj
    sangeetharanika


    Here my query is
    in the above names i want to find out the persons whoz names consist of exact 2 a's
    eg output:
    raveendra

    assume that the names are ename and table name is emp.

    Thanks in advance
    Plz mail this qury to chiru_y2k@yahoo.com

    Chiranjeevi

  2. #2
    Join Date
    Feb 2004
    Posts
    11

    Smile

    (select ename from emp where ename like '%a%a%')
    minus
    (select ename from emp where ename like '%a%a%a%');
    learning Oracle

  3. #3
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Jeethu - what if the person has 4 a's in there name?

    Chiranjeevi - When is your assignment due? You could use a PL/SQL block to accomplish your goal. You will basically need to process through the string counting A's. If you find only 2 return the string otherwise return NULL. I think that might be overkill but perusing the SQL commands I did not find anything that would be straight forward.

    Good luck.
    NOTE: Please disregard the label "Senior Member".

  4. #4
    Join Date
    Jan 2004
    Posts
    492
    Man this bugged the hell out of me because I couldnt think of a way to do it....

    Search for 'occurences within a string' at asktom.oracle.com


    http://asktom.oracle.com/pls/ask/f?p...1493455952998,


    Not sure if this link works - I couldnt get it to work on my computer - but search for that term and you will get the article I'm referrring to.
    Last edited by ss659; 02-05-04 at 09:09.

  5. #5
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    ss659 - Your abbreviated link gives a "page not found". Can you display the full link?
    NOTE: Please disregard the label "Senior Member".

  6. #6
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by Todd Barkus
    ss659 - Your abbreviated link gives a "page not found". Can you display the full link?

    I dont know why the link won't work either - Just search for 'occurences within a string' and it will come up.

  7. #7
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Thanks. I tried searching for "counting characters" but was stymied.
    NOTE: Please disregard the label "Senior Member".

  8. #8
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by Todd Barkus
    Thanks. I tried searching for "counting characters" but was stymied.
    Yea it took me a few to find it - It kind of bugs me that the links dont work right?

    But that site it amazing for questions like this that make you feel stupid -Stuff you know you should be able to do just cant

  9. #9
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Amen to that. Could stuff there at that site and link.

    Chiranjeevi - it looks like you have the pieces to solve your puzzle. Good luck.
    NOTE: Please disregard the label "Senior Member".

  10. #10
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    CREATE OR REPLACE FUNCTION CHARCOUNT (str IN VARCHAR2, chr IN CHAR) RETURN Number
    As
    Begin
    Declare
    chr_count Number := 0;
    pos Number := 0;
    Begin
    For i IN 1..(Length(str)) LOOP
    If (UPPER(SUBSTR(str, i, 1)) = UPPER(chr)) Then
    chr_count := chr_count + 1;
    End If;
    END LOOP;
    Return chr_count;
    End;
    End;
    /
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  11. #11
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    give someone a fish ... :-(
    NOTE: Please disregard the label "Senior Member".

  12. #12
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I'd have used TRANSLATE like this:
    PHP Code:
    SQLselect enamelength(ename)-length(translate(ename,'XA','X')) num_as
      2  from emp
    ;

    ENAME          NUM_AS
    ---------- ----------
    SMITH               0
    ALLEN               1
    WARD                1
    JONES               0
    MARTIN              1
    BLAKE               1
    CLARK               1
    SCOTT               0
    KING                0
    TURNER              0
    ADAMS               2
    JAMES               1
    FORD                0
    MILLER              0 

  13. #13
    Join Date
    Jan 2004
    Posts
    9

    Thumbs up thanks

    Thanks

  14. #14
    Join Date
    Jan 2004
    Posts
    9

    Thumbs up Thanks

    select ename from emp
    where len(ename)-len(replace(ename,'a',''))=2

    Thanks to Subhasish Ray

    Thanks allot

    Chiranjeevi

  15. #15
    Join Date
    Jan 2004
    Posts
    492

    Re: Thanks

    Originally posted by chiru_y2k
    select ename from emp
    where len(ename)-len(replace(ename,'a',''))=2

    Thanks to Subhasish Ray

    Thanks allot

    Chiranjeevi
    Chiranjeevi,

    Although yes that query works for your specific case, you are ignoring a HUGE part of the query. You need to account for the length of the string itself. In this case 'a' is only one character long, but if you were searching for a longer string, and it occurred many times, you would be in trouble.

    The correct query should be like thus (using a test case):

    Code:
    SELECT (length('TESTTESTTEST') -
           length(REPLACE('TESTTESTTEST', 'EST', ''))) / length('EST') 
      FROM dual
    Yields - 3 occurences of string 'EST' - which is correct.

    Your query would have been:

    Code:
    SELECT (length('TESTTESTTEST') -
           length(REPLACE('TESTTESTTEST', 'EST', ''))) 
      FROM dual
    Which would yield 9 occurences- certainly not accurate. The divided by length part is crucial to getting the accurate answer.

    Not to be a jerk either, but you should probably be thanking Tom on this one. Since it seems your syntax matches his exactly.

Posting Permissions

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