locked
List the full names of all employees in the format similar to “Alfred T. Pennyworth”. If they do not have a middle initial, omit the initial and have a space only RRS feed

  • Question

  • Hey guys, I am new to SQL and was wondering if anyone can help me with the above question. Listing the full names is easy with Aliases but I do not know how to omit the middle column in case of no middle initial. Can anyone please explain how to get that done please?
    Sunday, February 28, 2016 5:43 PM

Answers

  • First of all, you need to learn how to post intelligently. This is not a database design question - pay attention to the forum you choose for posting.  Generally speaking, the tsql and getting started forums are the most common forum for beginners. At the top of these particular forums are sticky posts that have suggestions for posting.  Please have a look.  And remember, no one can see what you see, can read your mind, or knows your database schema.  You need to provide sufficient information to your reader.

    Ignoring that, this sounds like a test question.  If you're new, then you must be learning in some fashion.  What you have learned?  What have you done so far?  Post that.  You should be able to at least post a basic query that selects the columns you need from your table.  That is a start.  

    Sunday, February 28, 2016 7:10 PM
  • Uri,

    See the other thread. Alex didn't have NULL, he had empty string... 


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, February 29, 2016 3:25 PM

All replies

  • First of all, you need to learn how to post intelligently. This is not a database design question - pay attention to the forum you choose for posting.  Generally speaking, the tsql and getting started forums are the most common forum for beginners. At the top of these particular forums are sticky posts that have suggestions for posting.  Please have a look.  And remember, no one can see what you see, can read your mind, or knows your database schema.  You need to provide sufficient information to your reader.

    Ignoring that, this sounds like a test question.  If you're new, then you must be learning in some fashion.  What you have learned?  What have you done so far?  Post that.  You should be able to at least post a basic query that selects the columns you need from your table.  That is a start.  

    Sunday, February 28, 2016 7:10 PM
  • Thanks for the advice, I will keep that in mind. I will repost this in that forum.

    Anyways, my question is 

    For an employee table in Pubs database with three different columns holding the First name, Middle Initial and Last names, I am supposed to display them in the format of “Alfred T. Pennyworth”. However, in case the middle initial column has no data, it has to omit the data and skip the column completely and display first name and middle name as “Alfred Pennyworth”.   How is this possible?

    I did the following but it still has the ".'' after the first name that's not desirable. I need to have the syntax omit the whole middle initial column in case it has a null value.

    select 
    [fname]+' '+[minit]+'.'+' '+[lname] as fullname
    from [dbo].[employee]

    Sunday, February 28, 2016 8:52 PM
  • See my reply to the other thread.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Sunday, February 28, 2016 9:11 PM
  • create table #t (c1 varchar(10),c2 varchar(10), c3 varchar(10))

    insert into #t values ('Alfred' ,'T', 'Pennyworth')
    insert into #t values ('John' ,null, 'Smith')


    select case when c2 is null then 
         c1 +' ' + c3 else c1+' ' +c2+'.'+c3 end  from #t 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, February 29, 2016 1:45 PM
  • Uri,

    See the other thread. Alex didn't have NULL, he had empty string... 


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, February 29, 2016 3:25 PM