none
SQL Script To Select and Manipulate Data Within The Table Field

    Question

  • This is again out of my depth, therefore I need SQL Guru to help me again, Thanks guys in advance.

    This is out of my depth thus I need SQL Gurus to help me. Thanks guys in advance!

    I have a data table in the following:

    Name              Expression
    FirstName           NULL
    LastName            NULL
    FullName          CONCAT(FirstName,LastName)
    Gender              NULL
    FullNameWGender   CONCAT(FullName,Gender)


    How do I use SQL statement to produce the following data taken from the table:

    FirstName
    LastName
    CONCAT(FirstName,LastName)
    Gender
    CONCAT(CONCAT(FirstName,LastName),Gender)

    The 

    CONCAT(CONCAT(FirstName,LastName),Gender)

    Is the result of CONCAT(FullName, Gender). But instead of selecting CONCAT(FullName, Gender), it checks within it whether there is any more level below it that it can access which have CONCAT and select it appropriately, thus producing CONCAT(CONCAT(FirstName, LastName), Gender))

    I am using T-SQL. 

    I do not expect a complete code but at least a starting point since I have no idea how to proceed with this problem.

    All and any help would be great.


    • Edited by Kiong_90 Friday, February 14, 2014 2:22 AM
    Friday, February 14, 2014 2:06 AM

Answers

  • You should be able to do either way:

    SELECT  [FirstName]
          ,[LastName]
          ,[Gender]
          ,Concat([FullName], [Gender]) as newcol,
    	  CONCAT(CONCAT(FirstName, LastName), Gender) newCol2
      FROM Youtable

    Friday, February 14, 2014 3:20 AM
    Moderator
  • try this

    SELECT  [FirstName]
          ,[LastName]
          ,Isnull([FirstName],'') + '  ' + isnull([LastName],'') as FullName
          ,[Gender]
          ,Isnull([FirstName],'') + '  ' + isnull([LastName],'') + ' ' + isnull([Gender],'') as FullNameWGender
          FROM tableName

    Ashish pandey

    Friday, February 14, 2014 6:57 AM

All replies

  • This forum is for T-SQL (SQL Server). Please find a right forum for your MySQL question. Thanks.
    Friday, February 14, 2014 2:12 AM
    Moderator
  • Ah. It was my mistake. I've reeditted the question. I am actually using SQL Server and T-SQL for this.
    Friday, February 14, 2014 2:22 AM
  • I'm not sure exactly what you want.  Could you give us some sample data and the result you want from that sample data?  That often makes it clearer what you are looking for.

    Tom

    Friday, February 14, 2014 3:09 AM
  • You should be able to do either way:

    SELECT  [FirstName]
          ,[LastName]
          ,[Gender]
          ,Concat([FullName], [Gender]) as newcol,
    	  CONCAT(CONCAT(FirstName, LastName), Gender) newCol2
      FROM Youtable

    Friday, February 14, 2014 3:20 AM
    Moderator
  • try this

    SELECT  [FirstName]
          ,[LastName]
          ,Isnull([FirstName],'') + '  ' + isnull([LastName],'') as FullName
          ,[Gender]
          ,Isnull([FirstName],'') + '  ' + isnull([LastName],'') + ' ' + isnull([Gender],'') as FullNameWGender
          FROM tableName

    Ashish pandey

    Friday, February 14, 2014 6:57 AM
  • The Concat function was introdued in SQL Server 2012. You don't need to check NULL value with ISNULL conversion any more.

    You can find more information about it from MSDN:

    http://technet.microsoft.com/en-us/library/hh231515.aspx

    Friday, February 14, 2014 9:34 PM
    Moderator