1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to display ename if ename exists more than once..

Discussion in 'SQL PL/SQL' started by Vicky, Aug 16, 2014.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    How to display ename if ename exists more than once. And how to display the half of the ename's in upper case and remaining lowercase?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Here is one way to do it:


    Code (SQL):

    SQL> SELECT UPPER(substr(ename, 1, instr(ename, ',', 1)-1))||', '||substr(ename, instr(ename,',',1)+2) FROM emptest
      2  /
    UPPER(SUBSTR(ENAME,1,INSTR(ENAME,',',1)-1))||','||SUBSTR(ENAME,INSTR
    (ENAME,',',1
    --------------------------------------------------------------------------------
    BORKO, Ralph


    SQL>
     

    There are probably other ways to do it as well.
     
    Vicky likes this.
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    For the part:
    There are several ways to do it, one of the "old school" way being:

    Code (SQL):
    SELECT e.ename,
      UPPER(SUBSTR(e.ename, 1,ROUND(LENGTH(e.ename)/2)))||
      LOWER(SUBSTR(e.ename, ROUND(LENGTH(e.ename)/2)+1)) new_name
    FROM emp e
    WHERE EXISTS
      (SELECT 1 FROM emp e2 WHERE e2.ename=e.ename AND e2.rowid > e.rowid) ;
    Note:
    For your request:
    I interpreted it as:

    If ename='James', then new_name='JAMes' though I agree with David's interpretation being what is generally required for names display, i.e. Name/Surname in Upper case and first name in lower case.

    But, I suppose your request is not really an operational requirement but more for learning purposes. :)

    As mentioned in David's post, there are other ways of doing it using for ex. REGEXP like functions.
     
    Vicky likes this.
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    It's not working in my machine.

    SELECT ename,UPPER(substr(ename,1, instr(ename, ',', 1)-1))||', '||substr(ename, instr(ename,',',1)+2) NEWNAME FROM emp;

    ENAME NEWNAME
    -------------------
    SMITH , MITH
    ALLEN , LLEN
    WARD , ARD
    JONES , ONES
    MARTIN , ARTIN
    BLAKE , LAKE
    CLARK , LARK
    SCOTT , COTT
    KING , ING
    TURNER , URNER
    ADAMS , DAMS
    JAMES , AMES


    And Rajen's query is working fine.

    SELECT e.ename,UPPER(SUBSTR(e.ename, 1,ROUND(LENGTH(e.ename)/2)))|| LOWER(SUBSTR(e.ename, ROUND(LENGTH(e.ename)/2)+1)) new_name
    FROM emp e

    ENAME NEW_NAME
    ----------------------
    SMITH SMIth
    ALLEN ALLen
    WARD WArd
    JONES JONes
    MARTIN MARtin
    BLAKE BLAke
    CLARK CLArk
    SCOTT SCOtt
    KING KIng
    TURNER TURner
    ADAMS ADAms
    JAMES JAMes
    FORD FOrd
    MILLER MILler
     
  5. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Jagadekara,

    This is because David's query relies on the presence of the "," character in the ename field (performed by "instr(ename, ',', 1)" instruction which looks for the 1st occurrence of the "," character and which returns 0 in your case).

    And your data for ename doesn't contain any comma.

    To check David's query try the following:

    Code (SQL):
    SQL> CREATE TABLE emptest AS SELECT 'Borko, Ralph' ename FROM dual;

    TABLE created.

    SQL> SELECT * FROM emptest;

    ENAME
    ------------
    Borko, Ralph

    SQL> SELECT UPPER(substr(ename, 1, instr(ename, ',', 1)-1))||', '||substr(ename, instr(ename,',',1)+2) FROM emptest;

    UPPER(SUBSTR(ENAME,1,INSTR
    --------------------------
    BORKO, Ralph
     
     
  6. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Tnx everyone., But the query below is not working, while I try to find the records which are present in the table more than once.

    select * from employees e1
    where exists (select 1 from employees e2 where e1.rowid>e2.rowid);

    Since, I'm not so sure about the functionality of Exists., Could U tel me wats the prblm with the query,,.?!
     
  7. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Vicky,

    This query will return (all rows -1) from the employees tables as there will always be 1 rowid > another one except the "last" record; so it will not display the "last" record having the greatest rowid.

    What you have missed in your query is the criteria which determines the duplicate, in your situation, ename.

    So please add (or copy what I proposed above), i.e., add the condition e1.ename= e2.ename (or the proper column name for employee name in your table)

    Code (SQL):
    SELECT * FROM employees e1
    WHERE EXISTS (SELECT 1 FROM employees e2 WHERE e1.ename=e2.ename AND e1.rowid>e2.rowid);