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!

Query for the following?????

Discussion in 'SQL PL/SQL' started by johnchennai, Jan 9, 2014.

  1. johnchennai

    johnchennai Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    100
    Table B having columns name, address, email

    In the email column I want to display before @

    eg : email is john@gmail.com means i want to display only john in email column.
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi john,

    Email column always have @gmail.com at the end?
     
  3. johnchennai

    johnchennai Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    100
    in case if the condition to show only name not '@gmail.com'
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    if that email column consists all values which have ends with @gmail.com then use following query.

    SELECT replace(email,'@gmail.com','') from B;
     
  5. johnchennai

    johnchennai Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    100
    bro thanks , its working...
     
  6. johnchennai

    johnchennai Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    100
    there may emails having @yahoo.com also or some other domai.. in this case
     
  7. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Try this...

    select substr(email,1,(select instr(email,'@')-1 from B))from B;
     
  8. johnchennai

    johnchennai Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    100
    error showing like

    ORA-01427: single-row subquery returns more than one row
     
  9. johnchennai

    johnchennai Active Member

    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    100
    if the table having one email its executes, if its having more than one email error showing as above
     
  10. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Yes i got it, so thinking... wait
     
  11. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    select substr(email,1,(select instr(email,'@')-1 from B alias1 where alias1.name=alias2.name))from B alias2;
     
  12. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi,

    Why you are using " select instr(email,'@')-1 ... " . directly you can use instr() inside substr()..

    check the below query.

    Code (SQL):
    SELECT SUBSTR(email,1,INSTR(email,'@')-1) FROM B

    Regards
    Sambasiva Reddy.K
     
    johnchennai and jagadekara like this.
  13. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Sambudu,

    Thanks for suggesting new idea... Actually I didn't think that way....