SQL: How to display the last name alone from the emp name where first name n lastname is in a single colum

50 pts.
Tags:
SQL
SQL functions
Is there a way to display the last name alone from the emp name where first name n lastname is in a single column?


Software/Hardware used:
Sql
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • TheRealRaven
    What DBMS is used? In general, it might be like:
    SUBSTR(nameColumn, POS(' ', nameColumn)+1)
    Other forms and functions are possible, and it might be better not to do it with SQL. Best would probably be not having both in a single column if they need to referenced separately.

    Names are somewhat difficult. Consider "C. Thomas Howell" or "Jean-Claude Van Damme".
    34,400 pointsBadges:
    report
  • carlosdl
    To get more accurate answers you need to specify the database system you are using, as asked by Raven.

    Also, you need to specify the criteria you want to use to determine what part of the name can be used as the first name.  Are you going to use a blank as the separator between the first name and the rest of the name?  If so, are you certain that it will work well for all of the names you have in your table?
    84,640 pointsBadges:
    report
  • ToddN2000
    There are a few ways to do this. We need more info to give a better response. When it comes to something like a name, it's always a good idea to use a comma to delimit the first from last. Using a blank can cause issues with a name like Mary Ann Smith. A blank delimiter will give the wrong result.
    131,500 pointsBadges:
    report
  • NARMATA

    Select substr(ename, instr(ename, ' ')) as "last name" 

    from emp;

    50 pointsBadges:
    report
  • carlosdl

    "Select substr(ename, instr(ename, ' ')) as "last name"

    from emp;"

    • This suggestion assumes Oracle is being used.
    • Additionally, it includes the blank space at the beginning of the resulting text.
    • It assumes that everything after the first blank will be the last name, which as mentioned by others is most likely not always the case.

    84,640 pointsBadges:
    report
  • CharlieBrowne

    Also, unexpected results when you have names like:

    John Smith JR     John Smith Phd   John Smith ???

    You would need to create a list of suffixes that you would want to ignore.

    62,385 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: