none
Combing Fields, Automatically (First_Name, Last_Name) RRS feed

  • Question

  • Hi, how do I combine columns in the same table?  I've tried different flavors using SELECT.  The columns I'm trying to combine are First_Name and Last_Name, with a space dividing the two.

    Thanks in advance.

    Wednesday, May 6, 2020 1:40 PM

All replies

  • Like this?

    SELECT First_Name + ' ' + Last_Name AS Complete_Name
    FROM dbo.yourTable


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, May 6, 2020 1:46 PM
    Moderator
  • a view on the table, having Olaf's derived column?

    jchang

    Wednesday, May 6, 2020 1:48 PM
  • That works when I run the query.  It still shows up as "Null" in the Name_Combined field.  Do I have to do something specific to save it and have it run automatically going forward when I create a new contact?

    SELECT First_Name + ' ' + Last_Name AS Name_Combined
    FROM dbo.contacts


    Wednesday, May 6, 2020 2:02 PM
  • NULL + something results in NULL again; you have to convert NULL values in empty string using ISNULL function

    SELECT ISNULL(First_Name, '') + ' ' + ISNULL(Last_Name, '') AS Name_Combined
    FROM dbo.contacts


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, May 6, 2020 2:04 PM
    Moderator
  • I'm new to SQL.  Both of these work.  Here's how I'm doing it.

    Within SMMS, I choose "Script Table as..." (shown below).  I add the code and it shows up, not in the table, but in a mini-table below.  I do not know the next step to save it or whatever I need to do.


    ELECT First_Name + ' ' + Last_Name AS Name_Combined FROM dbo.contacts

    SELECT ISNULL(First_Name, '') + ' ' + ISNULL(Last_Name, '') AS Name_Combined
    FROM dbo.contacts

    Wednesday, May 6, 2020 2:30 PM
  • As far as I understood you would like to add the computed value to the table itself? In such case you need to add a computed column. Try this:

    ALTER TABLE dbo.contacts ADD
    	Name_Combined AS ISNULL(First_Name, '') + ISNULL(' '+Last_Name , '');
    

    Regards

    Daniils

    Wednesday, May 6, 2020 2:55 PM
  • Thanks.  I will give that a try.  When I create a new contact, will this query stay in place?
    Wednesday, May 6, 2020 3:01 PM
  • Thanks.  I will give that a try.  When I create a new contact, will this query stay in place?

    Hi Mike Drevline,

    >> When I create a new contact, will this query stay in place?

    Yes. I check this in  my environment.


    Best regards,
    Cathy 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com



    Thursday, May 7, 2020 3:17 AM
  • Hi Mike Drevline,

    Any update? If the reply could help you, please mark the useful reply as answer.This can be beneficial to other community members reading the thread.
    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, May 8, 2020 6:06 AM